Commission Payment Spreadsheet

[trickeyd93]

Hi Guys, Thanks for reading.

My friend has recently been given a new pay structure and needs help putting together a simple spreadsheet to calculate how much he should be getting paid based on the saled.

What he does is door to door selling of Gas & Electric, and there are two types of sale. Direct Debit and PPM.

I did one which works and is being used at the moment. However he did ask to see if this could be simplified (for him)

He wants to be able to put in how many sales in DD & PPM into two columns with rows monday to friday. with total of sales at the bottom which will calculate his wages automatically.

The problem here is when he reaches 20 sales his commission increased and again at 59 and 75.

Can somebody help me to create a spreadsheet which will tot up the weekly sales and dependent on the total value calculate the "what should be" commission.

[JBeaucaire]

Is the commission simple? Meaning at 19 he gets $10 each, but at 20 he gets $12.50 each for all 20?

If so, this simple formula easily does what you've noted using VLOOKUP:

=VLOOKUP(D2, {0,10;20,12.5;60,15;75,15},2,1)

Notice the pairs of values in the inner array {}? Adjust those as needed to note where each rate starts.

[trickeyd93]

It isn't as simple as that I don't think. For the first 19 he gets 10$ then for the following 40 he gets 12.50

So for 60 he gets 190$ + 500$ for anything over he gets 15$. Would this formulae still apply?

[JBeaucaire]

No, that's a complex stacking commission. Let me lay that out. You need a better reference table.

[trickeyd93]

Ok thank you. Appreciate your help.

[JBeaucaire]

For this, there are multiple inner arrays. The first two are the same, indicating where each of the prior tiers END. The 3rd is the a building value.

10 = paid for ALL sales
2.50 = paid for all sales above the first tier, a bonus starting after 19
2.50 = paid for all sales above the second tier, a second bonus starting after 59.

PPM
=SUMPRODUCT(--($D2 >{0;19;59}), $D2 - {0;19;59}, {10;2.5;2.5})

DD
=SUMPRODUCT(--($G11 >{0;19;59;74}), $G11 - {0;19;59;74}, {15;3.75;3.75;2.5})

[trickeyd93]

This is fantastic. Thank you very much for your help.

[JBeaucaire]

As it appears you've reached a conclusion, I've marked this thread SOLVED for you.
FYI, this is done through the Thread Tools located above the first post in this thread. Thanks.