Forum Discussion
Formula Help
Hello,
I'm trying to figure out a formula with multiple factors, essentially I want to be able to enter a number in a cell and determine the required quantity (see screenshot attached):
- if the quantity is less than 600 then answer is 3
-if the quantity is between 600 and 10000 then the answer is the entered quantity divided by 200
-if the quantity is greater than 10000 and less than 40000 then the answer is (10000/200)+(the entered quantity less 10000 / 450)
-if the quantity entered is greater than 40000 then the answer is (10000/200)+(30000/450)+(the entered value less 40000/2000)
Hopefully that all makes sense
Well, I came up with a different answer. There no doubt is a more elegant way to do it, but this I based on a sheet I'd created to do US tax calculations. It involves a table, with variables to use in calculating the sample required based on a volume lookup (except I use MATCH) to determine which row of the table is applicable. I've attached the file for you.
Here's what the area looks like.
There's one formula, and this is it.
=CHOOSE(MATCH(A13,A2:A6,1),3,B3+INT((A13-H3)/F3),B4+INT((A13-H4)/F4),B5+INT((A13-H5)/F5))
The starting point is to identify which row to work with. The MATCH(A13,A2:A6,1) portion does that, getting a number from 1 to 4.
CHOOSE, then, picks the relevant value from the rest based on that 1-4 number
1 delivers the result 3
2 delivers the result B3+INT((A13-H3)/F3)
3 delivers the result B4+INT((A13-H4)/F4)
and 4, the result B5+INT((A13-H5)/F5)
4 Replies
This has the look of an assignment in a class you may be taking. So it would be best if, as much as possible, you figured it out for yourself. I will say this much: check out the VLOOKUP function. It's what I'd use. https://exceljet.net/excel-functions/excel-vlookup-function
And in the range_lookup field, select TRUE or 1
- Thanks for the hint, this is for work not school, I'm trying to determine how many soil samples are required based on the quantity provided as layout out by the provincial regulations
Well, I came up with a different answer. There no doubt is a more elegant way to do it, but this I based on a sheet I'd created to do US tax calculations. It involves a table, with variables to use in calculating the sample required based on a volume lookup (except I use MATCH) to determine which row of the table is applicable. I've attached the file for you.
Here's what the area looks like.
There's one formula, and this is it.
=CHOOSE(MATCH(A13,A2:A6,1),3,B3+INT((A13-H3)/F3),B4+INT((A13-H4)/F4),B5+INT((A13-H5)/F5))
The starting point is to identify which row to work with. The MATCH(A13,A2:A6,1) portion does that, getting a number from 1 to 4.
CHOOSE, then, picks the relevant value from the rest based on that 1-4 number
1 delivers the result 3
2 delivers the result B3+INT((A13-H3)/F3)
3 delivers the result B4+INT((A13-H4)/F4)
and 4, the result B5+INT((A13-H5)/F5)
