I would like a function to return a value that fits its range

[rpbsr]

For example:
0-$500 = 20%
501-$1000 = 15%
1001-$5000 = 12.5%

Thank you!

 

[Toppers]

One way:

=LOOKUP(A1,{0,501,1001},{0.20,0.15,0.125}) with cell formatted as %

or look at VLOOKUP function

 

[Ian]

=IF(B1<=500, 0.2,IF(B1<=1000,0.15,IF(B1<=5000,0.125,0))) and format as
percentage.

I've assumed that anything over the maximum in a range goes into the next
range (eg.$500.01) and that anything over 5000 results in 0.

 

[rpbsr]

That's perfect Ian. Thanks.

=IF(B1<=500, 0.2,IF(B1<=1000,0.15,IF(B1<=5000,0.125,0))) and format as
percentage.

I've assumed that anything over the maximum in a range goes into the next
range (eg.$500.01) and that anything over 5000 results in 0.

 

[Ian]

Glad it worked.

Thanks for the feedback.