Sales Commissions

Thanks for your help the other day, as sales managers do this one has put a real spanner in the works.

I have these formulas running in my spreadsheet but now he wants to add an accelerator bonus to the formula.

=IF(D21="","",IF(D21<1000,100,D21*10%))
=IF(D22="","",IF(D22<1000,100,D22*10%))
=IF(D23="","",IF(D23<1000,100,D23*10%))
=IF(D24="","",IF(D24<1000,100,D24*10%))
=IF(D25="","",IF(D25<1000,100,D25*10%))
=IF(D26="","",IF(D26<1000,125,D26*12.5%))
=IF(D27="","",IF(D27<1000,125,D27*12.5%))
=IF(D28="","",IF(D28<1000,125,D28*12.5%))
=IF(D29="","",IF(D29<1000,150,D29*15%))
=IF(D30="","",IF(D30<1000,150,D30*15%))
=IF(D31="","",IF(D31<1000,150,D31*15%))

So from the above when they enter a profit figure in cell D21 it gives them £100 if the profit is less than £1,000 and if greater it gives them 10% of the profit. After number 5 is sold they get 12.5% and so on up to 15%. Does anyone know how I can add to the above formulas so that once deal 5 is done it adds an extra 2.5% and applies it to the first 5 formulas, Then once deal 9 is done it applies 5% to the previous 8 deals done.

My understanding of your request is to include a 2.5% extra bonus once the fifth deal is done, and apply it to all of the first five deals. If this is accurate, you can replace your fifth formula with the following:

Please Login or Register  to view this content.

You will need to change E21:E24 if the formulas are in a different place.

Then for your second request, my understanding is after the ninth deal, you want to add an additional 5% to each of deals 1-8, but not the ninth deal. If this is accurate, you can replace the ninth formula with the following:

Please Login or Register  to view this content.

Again, you will need to change the references to column E with the appropriate column if needed.

HTH

Jason

Originally Posted by svaiskau

Maybe I'm misunderstanding, but couldn't you just nest the IF() statements?

=IF(D21="","",IF(D21<1000,100,IF(ISBLANK(D25),D21*10%,IF(ISBLANK(D29),D21*12.5%,D21*15%))))

The rest will be correct if you autofill them.

Edit: Also note the order of the IFs prevents them from running further downstream if you have less than 5 sales--it's taking as little additional processing as possible.

Thanks for this, it works just fine except if the profit for the deal is less than £1000 the formula does not work. It is only working if the profit is over £1000

Originally Posted by jasoncw

My understanding of your request is to include a 2.5% extra bonus once the fifth deal is done, and apply it to all of the first five deals. If this is accurate, you can replace your fifth formula with the following:

Please Login or Register  to view this content.

You will need to change E21:E24 if the formulas are in a different place.

Then for your second request, my understanding is after the ninth deal, you want to add an additional 5% to each of deals 1-8, but not the ninth deal. If this is accurate, you can replace the ninth formula with the following:

Please Login or Register  to view this content.

Again, you will need to change the references to column E with the appropriate column if needed.

HTH

Jason

Hi Jason, Thanks for help on this one, Took out the 102.5% part and changed e to d. It works a treat