| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I compare a result to a range of values?
I have a percentage (26.9%) that I need to compare to a table to see what
amount of bonus my group receives. (ie 0-22.5%=$7200, 22.6%-23.8%=$6600) and so on. So I need to be able to return a bonus value for whenever the percentage falls. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I compare a result to a range of values?
I don't understand the rules sufficiently wher you say etc but it souns like
you need a table of percentages/bonuses thus A B 22.5 7200 23.8 6600 25.9 6200 You then lookup a value (say) 23.0 in C1 with this formula =VLOOKUP(C1,A1:B3,2,TRUE) C1 in this example will return 7200 until it increases to 23.8 after which it will return 6600. Mike "UnisourceforNPH" wrote: I have a percentage (26.9%) that I need to compare to a table to see what amount of bonus my group receives. (ie 0-22.5%=$7200, 22.6%-23.8%=$6600) and so on. So I need to be able to return a bonus value for whenever the percentage falls. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I compare a result to a range of values?
Try VLOOKUP.
-- David Biddulph "UnisourceforNPH" wrote in message ... I have a percentage (26.9%) that I need to compare to a table to see what amount of bonus my group receives. (ie 0-22.5%=$7200, 22.6%-23.8%=$6600) and so on. So I need to be able to return a bonus value for whenever the percentage falls. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I compare a result to a range of values?
You need to use the lower boundary for each interval:
...........A............B 1........0.........7200 2.....22.6.......6600 3.....23.9.......???? Biff "Mike H" wrote in message ... I don't understand the rules sufficiently wher you say etc but it souns like you need a table of percentages/bonuses thus A B 22.5 7200 23.8 6600 25.9 6200 You then lookup a value (say) 23.0 in C1 with this formula =VLOOKUP(C1,A1:B3,2,TRUE) C1 in this example will return 7200 until it increases to 23.8 after which it will return 6600. Mike "UnisourceforNPH" wrote: I have a percentage (26.9%) that I need to compare to a table to see what amount of bonus my group receives. (ie 0-22.5%=$7200, 22.6%-23.8%=$6600) and so on. So I need to be able to return a bonus value for whenever the percentage falls. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I compare a result to a range of values?
why?
"T. Valko" wrote: You need to use the lower boundary for each interval: ...........A............B 1........0.........7200 2.....22.6.......6600 3.....23.9.......???? Biff "Mike H" wrote in message ... I don't understand the rules sufficiently wher you say etc but it souns like you need a table of percentages/bonuses thus A B 22.5 7200 23.8 6600 25.9 6200 You then lookup a value (say) 23.0 in C1 with this formula =VLOOKUP(C1,A1:B3,2,TRUE) C1 in this example will return 7200 until it increases to 23.8 after which it will return 6600. Mike "UnisourceforNPH" wrote: I have a percentage (26.9%) that I need to compare to a table to see what amount of bonus my group receives. (ie 0-22.5%=$7200, 22.6%-23.8%=$6600) and so on. So I need to be able to return a bonus value for whenever the percentage falls. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I compare a result to a range of values?
The "and so on" part always means at least two posts but here's an example.
=LOOKUP(A1,{22.6,22.6,23.9,24.7,25.8,26.9},{7200,6 600,6000,5500,5000,4500}) Gord Dibben MS Excel MVP On Tue, 12 Jun 2007 11:23:01 -0700, UnisourceforNPH wrote: I have a percentage (26.9%) that I need to compare to a table to see what amount of bonus my group receives. (ie 0-22.5%=$7200, 22.6%-23.8%=$6600) and so on. So I need to be able to return a bonus value for whenever the percentage falls. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I compare a result to a range of values?
The first interval is 0 to 22.5 = 7200
Using your formula and table, if C1 = 13.5 the result is #N/A. The correct result should be 7200. The second interval is 22.6 to 23.8 = 6600 Using your formula and table, if C1 = 22.6 the result is 7200. The correct result should be 6600. Biff "Mike H" wrote in message ... why? "T. Valko" wrote: You need to use the lower boundary for each interval: ...........A............B 1........0.........7200 2.....22.6.......6600 3.....23.9.......???? Biff "Mike H" wrote in message ... I don't understand the rules sufficiently wher you say etc but it souns like you need a table of percentages/bonuses thus A B 22.5 7200 23.8 6600 25.9 6200 You then lookup a value (say) 23.0 in C1 with this formula =VLOOKUP(C1,A1:B3,2,TRUE) C1 in this example will return 7200 until it increases to 23.8 after which it will return 6600. Mike "UnisourceforNPH" wrote: I have a percentage (26.9%) that I need to compare to a table to see what amount of bonus my group receives. (ie 0-22.5%=$7200, 22.6%-23.8%=$6600) and so on. So I need to be able to return a bonus value for whenever the percentage falls. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Range of Values Returns One Result | Excel Worksheet Functions | |||
| Compare to a range of values using "from/to" | Excel Discussion (Misc queries) | |||
| Compare alpha and numeric values within a range | Excel Worksheet Functions | |||
| Compare cell = one result | Excel Discussion (Misc queries) | |||
| compare one row to another and sum the result | Excel Discussion (Misc queries) | |||
Linear Mode
