formula to return a $ amount for a range of %

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

michelledean - 12 Aug 2007 02:22 GMT

I am currently working on bonus items and my criteria is as follows:

Percentage Bonus amount
78.0% or more $4,000
76.0% - 77.99% $3,000
74.0% - 75.99% $2,000
72.0% - 73.99% $1,000
71.99% or less $0.00

Cell D in excel has the % and cell E will need to return the dollar amount...
can you provide a formula that would return an amount for the corresponding
bonus amount? Help is much appreciated.

Reply to this Message

T. Valko - 12 Aug 2007 02:51 GMT

Try this:

=(MATCH(D1,{0,0.72,0.74,0.76,0.78})-1)*1000

Signature

Biff
Microsoft Excel MVP

>I am currently working on bonus items and my criteria is as follows:
>
[quoted text clipped - 10 lines]
> corresponding
> bonus amount? Help is much appreciated.

Reply to this Message

Max - 12 Aug 2007 08:10 GMT

Another way using VLOOKUP

Assume lookup percentages in D2 down

In E2:
=VLOOKUP(D2,{0,0;0.72,1000;0.74,2000;0.76,3000;0.78,4000},2)
Copy E2 down

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

>I am currently working on bonus items and my criteria is as follows:
>
[quoted text clipped - 10 lines]
> corresponding
> bonus amount? Help is much appreciated.

Reply to this Message