Please help with formula

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
 
G

Guest

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
 
G

Guest

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)
 
G

Guest

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

HELP!! New to Excel. Need complex SUMIF. 0
please suggest a formula 2
Need a formula to sum quantities by part number? 3
Calculate Amount with Varying Percentages per Thousands 2
need help with formula 5
Highlight Five Values With Conditional Formula 1
Excel 07 - Formula won't calculate - please help 4
Pivot Table-Calculate Percentage of Parent Row Item 1

Top