[Solved] IF, Between, Less Than, Then Add ...

Discuss the spreadsheet application

[Solved] IF, Between, Less Than, Then Add ...

Postby quarkrad » Tue Apr 09, 2019 5:42 pm

Apologies - been trying to work out what to do but struggling to even ask the right question in help pages. In table 3, B19, C19 and D19 obtain values from another sheet (not given in this example). The length value in C19 can be anything between 80 and 1200. However, depending on the size type (1, 2 or 3) and actual value of C19 an additional value of £5, £10, £10, or £20 could be added to D19 in E19. I have got so far down many avenues but my lack of knowledge keeps stumping me with too many variables. Any pointers or help would be much appreciated.
Attachments
Book1.ods
(15.92 KiB) Downloaded 12 times
Last edited by Hagar Delest on Tue Apr 09, 2019 9:51 pm, edited 1 time in total.
Reason: tagged solved
libreoffice 5.4.1.2 on ubuntu 16.04
quarkrad
 
Posts: 59
Joined: Mon Nov 03, 2008 10:01 am

Re: Can't Ask Question, IF, Between, Less Than, Then Add ...

Postby RusselB » Tue Apr 09, 2019 6:15 pm

Where in Table 3 is the size information from Table 1?
How is the information from sheet Data used in any of these tables?
All of your information is hard coded, so any formulas that you were using to obtain the relevant information are lost to us, and while a formula that references a different spreadsheet would return an error without that spreadsheet, the formula used may be helpful.
OpenOffice 4.1.7 and LibreOffice 6.0.6.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 5498
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Can't Ask Question, IF, Between, Less Than, Then Add ...

Postby Villeroy » Tue Apr 09, 2019 6:37 pm

Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27215
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Can't Ask Question, IF, Between, Less Than, Then Add ...

Postby jeffs12 » Tue Apr 09, 2019 6:42 pm

Ignoring how that data gets into table 3,I believe your question is "How do I evaluate the size type and total length and add a value to the price depending on those two variables?"

My answer is with an nested if and lookup function. I modified your table one to work with the lookup function and eliminated the data validity from cell b19 so that I could test my formula. I believe that the formula in E19 is what you are looking for.
Attachments
Sample.ods
(13 KiB) Downloaded 16 times
Last edited by jeffs12 on Tue Apr 09, 2019 8:14 pm, edited 1 time in total.
AOO 4.1.3 on M$ Windows 7/10
jeffs12
 
Posts: 39
Joined: Wed Mar 19, 2008 9:48 pm

Re: Can't Ask Question, IF, Between, Less Than, Then Add ...

Postby quarkrad » Tue Apr 09, 2019 7:33 pm

jeffs12 - wow, that did it. Many, many thanks. I'm through your formula now to (try and) fully understand it.
libreoffice 5.4.1.2 on ubuntu 16.04
quarkrad
 
Posts: 59
Joined: Mon Nov 03, 2008 10:01 am


Return to Calc

Who is online

Users browsing this forum: MSN [Bot] and 27 guests