Need a formula to calculate Average Sales Commission

  • Thread starter Thread starter sam1259
  • Start date Start date
S

sam1259

Alf, I pasted all three formulas into my excel program. They all pul
the same numbers except the short version. For some reason th
percentage increases to over 100% at 31,000 but corrects itself ove
60,000.
I was also wondering if you have any ideas on if I want to use differn
numbers for the volume breaks and percentages.
(ie:pull the info from another sheet or cell)

A1 = 30,000 B1 = 50%
A2 = 60,000 B2 = 65%
A3 = 90,000 B3 = 80%
B4 = 90%

I was thinking that if we decide to change the first sales volume brea
to say, 25,000 (which means we could insert that into the A1 cel
instead) or change the 2nd level of percentage to 70% and Change th
number in B2.
I'm not sure if this makes sence or not. Any help is appreciated.
Scot
 
Hi!

Forget about the short version: it is wrong.

So far as your other points are concerned, the reason I gave you th
second version was to point up where the changes would be needed if yo
chose different bands or different percentages.

For example, you could replace each occurrence of 30000, 29999, 2999
respectively by references to cells with appropriate band values i
them. LIkewise, the various percentages could refer to cells wit
suitable values in them.
A tidy table which you can update if you need to (maybe out of sight i
you don't want to do it too often?) could give you total control ove
the inputs.

If, as you sketched, you want these values in A1..A3 and B1...B4, th
formula might look like this:

=IF(A10<$A$1,A10,$A$1)*$B$1+IF(A10-IF(A10<$A$1,A10,$A$1)<$A$2,A10-IF(A10<$A$1,A10,$A$1),$A$2)*$B$2+IF((A10-IF(A10<$A$1,A10,$A$1))-IF(A10-IF(A10<$A$1,A10,$A$1)<$A$2,A10-IF(A10<$A$1,A10,$A$1),$A$2)<$A$3,(A10-IF(A10<$A$1,A10,$A$1))-IF(A10-IF(A10<$A$1,A10,$A$1)<$A$2,A10-IF(A10<$A$1,A10,$A$1),$A$2),$A$3)*$B$3+(((A10-IF(A10<$A$1,A10,$A$1))-IF(A10-IF(A10<$A$1,A10,$A$1)<$A$2,A10-IF(A10<$A$1,A10,$A$1),$A$2))-IF((A10-IF(A10<$A$1,A10,$A$1))-IF(A10-IF(A10<$A$1,A10,$A$1)<$A$2,A10-IF(A10<$A$1,A10,$A$1),$A$2)<$A$3,(A10-IF(A10<$A$1,A10,$A$1))-IF(A10-IF(A10<$A$1,A10,$A$1)<$A$2,A10-IF(A10<$A$1,A10,$A$1),$A$2),$A$3))*$B$4

The absolute references ($A$1 etc) allow you to copy the formula dow
or across. So, for example, you could put this formula in B10: copy i
down to, say B25. Put amounts like 80000,100000,120000, etc in A10 dow
to A25. Put = B10/A10 in cell C10 and copy it down to C25 and format a
percentage..

Provided you have the data in A1:B4 as you suggested, this will giv
you a picture of commission levels at various sales levels.

Add to this the different values you can now put in A1:B4 and you coul
begin to have (productive?) fun. Just be careful you don't foment
revolution among the sales staff...

Check the formula carefully: only A10 has no dollar signs.

Al
 
Back
Top