How do I compare a result to a range of values?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
You need to use the lower boundary for each interval:

...........A............B
1........0.........7200
2.....22.6.......6600
3.....23.9.......????

Biff
 
why?

T. Valko said:
You need to use the lower boundary for each interval:

...........A............B
1........0.........7200
2.....22.6.......6600
3.....23.9.......????

Biff
Click to expand...
 
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,6600,6000,5500,5000,4500})


Gord Dibben MS Excel MVP
 
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
 
Back
Top