Page 1 of 1

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

PostPosted: Tue Apr 09, 2019 5:42 pm
by quarkrad
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.

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

PostPosted: Tue Apr 09, 2019 6:15 pm
by RusselB
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.

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

PostPosted: Tue Apr 09, 2019 6:37 pm
by Villeroy

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

PostPosted: Tue Apr 09, 2019 6:42 pm
by jeffs12
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.

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

PostPosted: Tue Apr 09, 2019 7:33 pm
by quarkrad
jeffs12 - wow, that did it. Many, many thanks. I'm through your formula now to (try and) fully understand it.