Apr 04 2022 05:09 AM
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
Apr 04 2022 10:07 AM
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
Apr 05 2022 02:32 AM
Apr 05 2022 07:37 AM
Solution
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)
Apr 05 2022 07:37 AM
Solution
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)