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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

UnisourceforNPH - 12 Jun 2007 19:23 GMT

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 to this Message

Mike H - 12 Jun 2007 19:33 GMT

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

> 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 to this Message

T. Valko - 12 Jun 2007 19:47 GMT

You need to use the lower boundary for each interval:

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

Biff

>I don't understand the rules sufficiently wher you say etc but it souns
>like
[quoted text clipped - 18 lines]
>> (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 to this Message

Mike H - 12 Jun 2007 19:58 GMT

why?

> You need to use the lower boundary for each interval:
>
[quoted text clipped - 27 lines]
> >> (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 to this Message

T. Valko - 12 Jun 2007 20:11 GMT

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

> why?
>
[quoted text clipped - 31 lines]
>> >> to
>> >> return a bonus value for whenever the percentage falls.

Reply to this Message

David Biddulph - 12 Jun 2007 19:44 GMT

Try VLOOKUP.

Signature

David Biddulph

>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 to this Message

Gord Dibben - 12 Jun 2007 20:09 GMT

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

>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 to this Message