[carls007]

Hello, really stuggling here,

I need to calculate a commission based upon the following scenario.
(5% of the Total sales above £2000 upto £5000 and 18% on the remaining over £5000.)

I think i have the latter working ok?

I have attched a spreadsheet with the layout and formulas so far, if this can be used.


Any takers??

Thank you so much in advance,

[etaf]

you can use a nested IF

IF( Cell > 5000 , Cell * 0.18 , If ( Cell > 2000 , Cell * 0.05 , 0 ) )

looking at the spreadsheet wasn't sure where the calculations where made

can you explain a little more please

[carls007]

Hello,

Thank you for helping me with this.

I am sorry its my explaining.

We would like to pay commisson as the following:

0 - 2k no percentage on sales
2k - 5k is at 5%
5k + is at 18%


So based on a sales person generating 10k

0 - 2k no percentage on sales
2k - 5k at 5% = £150
5k - 10k at 18% = £950 So that total payout would be £1050.


Based on how the spreadsheet stands at the moment its working out like this:

0-2k no percentage
2k - 8k at 5% = £400
5K - 10k 18% = £900 making the total payout £1300

(which means we are paying commission twice between 5k - 8k)

I have attached the spreadsheet again and highlighted in blue where your formulas are.

So this part of the formula =IF(B13>2000,B13*0.05,0) im thinkning would need to have some sort of condition in there so that it only works out a 5% commission from £2000 - £5000?

Is this possible?

is it my spreadsheet, is there an easier way to work this out other than how i have done this, i will need to record each sale and its amount etc and have a total sales and commission earned but if there is an easier layout im all ears!!

Many thanks again

Kind regards

carly

[etaf]

OK - I will work on the spreadsheet

in principle then
if the amount is greater than 5000 - I take the amount and subtract the 5000 - that part is paid at 18% - now because its greater then 5000 - we know we habe to also pay the 2000-5000 range - which is 3000 @ 5%

If the amount the condition for 5000 does not apply - then we test to see if over 2000 , if it is then its the cell -2000 which we then pay at 5%

IF( Cell > 5000 , ((Cell-5000) * 0.18)+(3000 *0.05) ) , If ( Cell > 2000 , ( (Cell-2000) * 0.05) , 0 ) )

hope I have explained OK - will now go and look at the spreadsheet

[carls007]

Hey, i put that into the spreadsheet and that works!!

Think i have incorporated it in ok, i will send this to you tomorrow!

Thank you!!

[etaf]

OK - havent had a chance to look at the spreadsheet so glad that worked out

so if they get £2001 they only get commission on the £1 correct
also I have not include =
so if it is 2000 that will not be included
and 5000 will be at 5%