CODEWITHSUNDEEP
excelspreadsheetpiecewise
oli_taz
oli_taz

Reputation: 199

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

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)

Upvotes: 2

Views: 212123

Answers (3)

starball
starball

Reputation: 51372

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.

Upvotes: 0

Carsten Massmann
Carsten Massmann

Reputation: 28226

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.

Upvotes: 6

orgtigger
orgtigger

Reputation: 4132

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.

Upvotes: 1

Related Questions