Excel Multiple Ranges - need different answers for each range (step function)

Question

How do you return different values in a cell based on which range the value entered in another cell comes under? Specifically, I am trying to make a step function.

For example:

1. IF G2 is ABOVE "0" BUT BELOW "1" THEN display "0.1"
2. IF G2 is ABOVE "0.99" BUT BELOW "5" THEN display "0.15"
3. IF G2 is ABOVE "4.99" BUT BELOW "15" THEN display "0.2"
4. IF G2 is ABOVE "14.99" BUT BELOW "30" THEN display "0.5"
5. IF G2 is ABOVE "29.99" BUT BELOW "100" THEN display "1.0"
6. IF G2 is ABOVE "99.99" THEN display "1.30"

So IF G2 was "£18.75" then the cell that this formula is entered in would display "£0.50" based on the value's above.

(bear in mind that this is specific to my spreadsheet and was for calculating prices i.e. 0.99 = £0.99)

Cullan

What did you use for your answer? VLookup with a sorted table of lookup values and their corresponding results? – Ranit

Answer 1

Following @oli_taz's suggestion, here is a slightly more robust solution that can deal with any input:

=IF(D4<F4, 0, VLOOKUP(D4,F4:G9,2))

with the range F4:G9:

0    0.1
1    0.15
5    0.2
15   0.5
30   1
100  1.3

and D4 being the value in question, e.g. 18.75 -> result: 0.5

Numbers smaller than 0 will return 0 and numbers larger than 100 will return 1.3.

Worldwide

I don't see anything in the asker's question indicating that they needed to have the range bounds be parametric on values from other cells. I personally would have asked for clarification first, since having this extra machinery could possibly be undesirable for the asker or for other people who see these posts, but that's just me. – Oddment

As this was a long time ago I cannot answer with confidence now but I believe I needed the formula to calculate selling fees for products, so a lookup table would have been a better option (despite me trying to hard-code the ranges at the time). I believe this is still the accepted answer besides it not handling values outside this range. – Cullan

@Carsten Massmann it might be good to add range checks and a default value? i.e. for a lower bound check =IF(D4<F4, 0, VLOOKUP(D4,F4:G9,2)). I didn't want to edit the answer if there is a better way to handle these edge cases for upper and lower bounds? – Cullan

Answer 2

Nested if's in Excel Are ugly:

=If(G2 < 1, .1, IF(G2 < 5,.15,if(G2 < 15,.2,if(G2 < 30,.5,if(G2 < 100,.1,1.3)))))

That should cover it.

Eminence

Answer 3

You could use the VLOOKUP function (probably a better choice if your range bounds are already stored in cells). Say you have lower bounds of each range in column A (these need to be sorted in ascending order), and values to map to in column B, =VLOOKUP([input],A:B,2).

I don't think VLOOKUP supports scenarios where the lowest range has no lower bound.

---

If your range bounds are something that will probably never change and you don't want to put them in cells, you should be able to use the IFS function.

Quoting docs, the function signature is: =IFS([Something is True1, Value if True1,Something is True2,Value if True2,Something is True3,Value if True3).

If you want to have a default value if nothing matches, you can add a final argument pair: TRUE, "nothing matched".

If multiple of the condition expressions evaluate to true, the first one (from left to right) is used. If in your step function, each consecutive range starts where the previous on ended, you can use this as a trick to write less. Ex. instead of =(AND(0.0<=A1,A1<1.0), "range 1", AND(1.0<=A1,A1<2), "range 2", ...), I think you should be able to write =(AND(0.0<=A1,A1<1.0), "range 1", A1<2, "range 2", ...) and get the same thing.

If you weren't matching ranges and instead were matching exact values, you would instead use the SWITCH function.

Oddment