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:
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)
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
.