Tiered Pay Scale Calculations
 

I am calculating a multi tiered pay scale based on earnings:
0-200,000 net earnings = a bonus of 10% of the earnings
200,001-400,000= 15% bonus for this tier
400,001-1,000,000= 20% bonus for this tier
and so on...
I want to be able to enter a value in a cell and have it calculate the total
bonus. IN this example a Net Earnings of $500,000 should result in a bonus
of aprox. $70,000 ($20,000 for first tier, $30,000 for second, and $20,000
for the third).

Please Help

Hi

might be an easier way, but here's one option
=IF(A1200000,IF(A1400000,(A1-400000)*0.2+50000,(A1-200000)*0.15+20000),A1*0.1)

Cheers
JulieD

"AZapata" wrote in message
...
I am calculating a multi tiered pay scale based on earnings:
0-200,000 net earnings = a bonus of 10% of the earnings
200,001-400,000= 15% bonus for this tier
400,001-1,000,000= 20% bonus for this tier
and so on...
I want to be able to enter a value in a cell and have it calculate the
total
bonus. IN this example a Net Earnings of $500,000 should result in a
bonus
of aprox. $70,000 ($20,000 for first tier, $30,000 for second, and $20,000
for the third).

Please Help

On Wed, 8 Dec 2004 18:35:04 -0800, "AZapata"
wrote:

I am calculating a multi tiered pay scale based on earnings:
0-200,000 net earnings = a bonus of 10% of the earnings
200,001-400,000= 15% bonus for this tier
400,001-1,000,000= 20% bonus for this tier
and so on...
I want to be able to enter a value in a cell and have it calculate the total
bonus. IN this example a Net Earnings of $500,000 should result in a bonus
of aprox. $70,000 ($20,000 for first tier, $30,000 for second, and $20,000
for the third).

Candidate for a VLOOKUP table

Take a look at the commission example he

http://www.mcgimpsey.com/excel/variablerate.html


In article ,
"AZapata" wrote:

I am calculating a multi tiered pay scale based on earnings:
0-200,000 net earnings = a bonus of 10% of the earnings
200,001-400,000= 15% bonus for this tier
400,001-1,000,000= 20% bonus for this tier
and so on...
I want to be able to enter a value in a cell and have it calculate the total
bonus. IN this example a Net Earnings of $500,000 should result in a bonus
of aprox. $70,000 ($20,000 for first tier, $30,000 for second, and $20,000
for the third).

Please Help