SOLVED

Formula Help

Copper Contributor

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 Excel Formula Help.png

 

4 Replies

@Travis_D1990 

 

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
best response confirmed by VI_Migration (Silver Contributor)
Solution

@Travis_D1990 

 

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.

mathetes_0-1649168988740.png

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)

 

That works thank you!!!
1 best response

Accepted Solutions
best response confirmed by VI_Migration (Silver Contributor)
Solution

@Travis_D1990 

 

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.

mathetes_0-1649168988740.png

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)

 

View solution in original post