How do you calculate a percentage of sum when there are differing parameters?

[TONI12]

Hi Guys,

Another problem I need solving.

I have attached a commission form that I need to add formulas to.

The commission rate is;
There are 2 commission rates and you only earn commission on anything you make over £5000
when the total gross margin (TGM) is over £5,000 but less than £25,000 the commission is calculated as (18% of(TGM-5000)).
when the TGM is over £25,000 the commission is calculated as (25% of (TGM-5000)).

My problem is...
- In C25 I need a formula that calculates what the individuals commission rate is based on their total gross margin - (whether it is 18% or 25%)
- In C27 I need it to calculate what commission is due based on the commission rate in C25, taking into account the £5000 threshold (which you have to minus from any gross margin)

I know this sounds complicated but if you need any clarification please ask.

Any help will be welcome.

Thanks in advance!

[ARGK]

Hi
Commission rate formula:
=IF(AND($E$25>5000,$E$25<25000),"18%",IF($E$25>=25000,"25%"))
Commission due formula:
=IF(AND($E$25>5000,$E$25<25000),"18%",IF($E$25>=25000,"25%"))
Tony

[TONI12]

Hi ARGK,

Thank you for the help.

The first formula works for commission rate but the second formula for commission due is exactly the same as the first and it calculates as a percentage not in pounds.

I know the second formula is and 'IF' and it is different from the first.

[ARGK]

Hi
Sorry - cut and pasted the wrong formula. Here is the correct one:

=IF(AND($E$25>5000,$E$25<25000),($E$25-5000)*18%,IF($E$25>25000,($E$25-5000)*25%))

Tony

[TONI12]

Thank you ARGK...works perfect... you have saved me a lot of time!

[ARGK]

Thanks for the feedback.