| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I would like a function to return a value that fits its range
For example:
0-$500 = 20% 501-$1000 = 15% 1001-$5000 = 12.5% Thank you! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
I would like a function to return a value that fits its range
One way:
=LOOKUP(A1,{0,501,1001},{0.20,0.15,0.125}) with cell formatted as % or look at VLOOKUP function "rpbsr" wrote: For example: 0-$500 = 20% 501-$1000 = 15% 1001-$5000 = 12.5% Thank you! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
I would like a function to return a value that fits its range
=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 -- "rpbsr" wrote in message ... For example: 0-$500 = 20% 501-$1000 = 15% 1001-$5000 = 12.5% Thank you! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
I would like a function to return a value that fits its range
That's perfect Ian. Thanks.
"Ian" wrote: =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 -- "rpbsr" wrote in message ... For example: 0-$500 = 20% 501-$1000 = 15% 1001-$5000 = 12.5% Thank you! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
I would like a function to return a value that fits its range
Glad it worked.
Thanks for the feedback. -- Ian -- "rpbsr" wrote in message ... That's perfect Ian. Thanks. "Ian" wrote: =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 -- "rpbsr" wrote in message ... For example: 0-$500 = 20% 501-$1000 = 15% 1001-$5000 = 12.5% Thank you! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Passing a range name as an argument to the Index Function | Excel Discussion (Misc queries) | |||
| Need some comments on my Utility_Move class module. | Excel Worksheet Functions | |||
| calculating return in a range | Excel Worksheet Functions | |||
| Excel option to store trendline's coefficients in cells for use | Charts and Charting in Excel | |||
| Formula to list unique values | Excel Worksheet Functions | |||
Hybrid Mode
