excelspreadsheetpiecewise

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)


Solution

  • 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.