Please help with formula

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

Guest

I want to be able to calculate different percentages of a number.
Example:
The premium is $50,000
You get 7% commission of the first $20,000 of premium.
You then get 5% of the next $10,000 of premium.
Then you get 4% of the remaining amount.
We want to set the formula so we can put in any premium or percentages to
automatically calculate.
Thanks,
DeNise
 
Let's assume your premium is stored in cell A1, then this formula should
calculate your commission based on the criteria you provided.

=MIN(A1,20000)*0.07+MIN(MAX(A1-20000,0),10000)*0.05+MAX(A1-30000,0)*0.04

HTH,
Elkar
 
Put your premium in A1, then:

=0.07*MIN(A1,20000)+(A1>20000)*0.05*MIN(A1-20000,10000)+(A1>30000)*0.04*(A1-30000)
 
Thanks alot.

Elkar said:
Let's assume your premium is stored in cell A1, then this formula should
calculate your commission based on the criteria you provided.

=MIN(A1,20000)*0.07+MIN(MAX(A1-20000,0),10000)*0.05+MAX(A1-30000,0)*0.04

HTH,
Elkar
Click to expand...
 
Back
Top