Sales commission formula

Hi,

I am having difficulty creating a formula to work out commission.

The scenario is as follows:

$0 - $400,000 commission is charged at 3.95%

$400,000 + commission is 2%

How can i create a formula which can workout what commission is earnt for any price?

For Example, on a $500,000 sale:
First $400,000 is $15,800
Further $200,000 is $4,000
Giving a total commission of 19,800.

Many Thanks!!

For Example, on a $500,000 sale:

I suppose you meant a 600000 sale :-)

Try this formula =SUMPRODUCT(--(A1>{0,400000}),(A1-{0,400000}), {0.0395,-0.0195})

As you can see, 0 and 400000 are the limits for your commission
.0395 is the commission up to 400 k
-0.0195 is the difference between the rate for the second layer (.002)and for the first layer
So it is .002-0.0.395 = -.00195

Hey, yes sorry, I meant $600,000 sale.

Thank you so much for your reply, Im pretty sure Ive got it working now.

Much appreciated!!

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save