Commission Calculator

**TISSO** · 12-09-2011, 11:21 AM

As it states on the sheet, I am trying to continue the calculations to another row whilst maintaining the integrity of the profit bandings. My brain has fried from over thinking the solution. I know it's there somewhere!
Any help greatly appreciated.

COMMISSION CALCULATOR.xls

**alansidman** · 12-09-2011, 12:48 PM

I've looked at your spreadsheet and don't understand what the formulae are trying to do. Perhaps, if you explained your logic in simple English it might help. Is there a cumulative relationship that travels on to the succeeding month? Why only 1000 in the second month when your table shows 2000. Very confusing. Not intuitive.

**TISSO** · 12-09-2011, 01:01 PM

Hello,

I am trying to achieve a sheet that shows progression through the commission bandings per quarter. In other words, an employee earns a basic wage for the first month of the quarter and in the second and third months the wage increases as their gross profit earnings for the company cumulate. If they were to earn 9000 gp in month one this would take them to the second commission bracket, earning 3.5%. If they were to earn the same in the second month of the quarter, 1000 gp of that would complete the 10,000 threshold, therefore carrying over 8000 into the following bracket which is 5%. Hope this helps and hope that you can help!

**shg** · 12-09-2011, 02:07 PM

       ----A----- ---B--- ---C--- ---D--- ---E--- ---F--- ---G--- ---H--- ---I---
   1       GP      Comm                                                          
   2   Do not Use                                                                
   3         -       0.0%                                                        
   4       2,000     3.5%                                                        
   5      10,000     5.0%                                                        
   6      25,000     6.0%                                                        
   7      40,000     8.0%                                                        
   8      60,000    10.0%                                                        
   9                                                                             
  10                                                                             
  11   Do Not Use Month1  Month2  Month3  Month4  Month5  Month6  Month7  Month8 
  12               9,000   9,000   9,000   9,000   9,000   9,000   9,000   9,000 
  13                 245     435     470     540     640     720     780     900

The formula in B13 and copied across is

=SUMPRODUCT((SUM($B12:B12) > $A$3:$A$8) * (SUM($B12:B12) - $A$3:$A$8), $B$3:$B$8 - $B$2:$B$7) - SUM($A13:A13)

I've edited your thread title. Please take a few minutes to read the forum rules before posting again.

Thanks.

**TISSO** · 12-10-2011, 06:39 AM

Thank you for your help. After copying your formula and layout I get the #VALUE! response. I have attached the sheet I used. I can't see any difference between what you quoted in your post and what I have applyed to my sheet.

Many thanks.

Book1.xls