Need Excel Graduated Commission Formula

I have a graduated commission schedule table (excel attached).  I am trying to figure out a formula to calculate the colored sections to show how this would effect commission payments for the company and sales agent.

Any help would be greatly appreciated :)


Thanks in advance.
Commission-Split-Comparison-EDIT-EE.xlsx
DEBROCKEAsked:
Who is Participating?
 
Glenn RayConnect With a Mentor Excel VBA DeveloperCommented:
You've actually set up a very good table with the AGC schedule on top that will allow you to use a VLOOKUP to return the correct percentages and therefore the dollar amounts.

Enter these functions in the cells indicated and copy down:
F17:  =E17*VLOOKUP(E17,$A$5:$D$13,3,TRUE)
G17:  =E17*VLOOKUP(E17,$A$5:$D$13,4,TRUE)

This is doing a approximate match lookup of the AGC amount in column E against the values in A5:A13.  Because your values are sorted, it returns the percentage on the same row as the value less than or equal to the AGC amount.

Modified file attached.

Regards,
-Glenn
EE-Commission-Split-Comparison-EDIT.xlsx
0
 
ProfessorJimJamCommented:
perhaps if you want to learn it, how you can do it by yourself then here is a good explanation

can be done using SUMPRODUCT

http://www.mcgimpsey.com/excel/variablerate.html
0
 
DEBROCKEAuthor Commented:
Thanks.  I will take a look.  Just looking for a quicker solution right now.  

I was using a long IF statement (like below) and editing the formula for each cell I need it.  But doing something wrong.  If any expert can help, it would greatly be appreciated.

=IF(E17>B12,(E17-B12)*C13,0)
+IF(E17>B11,(MIN(E17,B12)-B11)*C12,0)
+IF(E17>B10,(MIN(E17,B11)-B10)*C11,0)
+IF(E17>B9,(MIN(E17,B10)-B9)*C10,0)
+IF(E17>B8,(MIN(E17,B9)-B8)*C9,0)
+IF(E17>B7,(MIN(E17,B8)-B7)*C8,0)
+IF(E17>B6,(MIN(E17,B7)-B6)*C7,0)
+IF(E17>B5,(MIN(E17,B6)-B5)*C6,0)
+MIN(E17,B5)*C5
0
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
DEBROCKEAuthor Commented:
Thank you, the VLOOKUP worked for me.  I modified it slight to generate what I wanted as I didn't explain it perfectly.  For future people, here is the modified table I used.
Commission-Split-Comparison-EDIT-UPDATE.
0
 
Glenn RayExcel VBA DeveloperCommented:
Sorry I didn't catch that (about the Company $ amounts), but glad I could help.

I recommend using absolute cell references for some of your formulas so that it will be easier to duplicate and add new ones.  For example:
D17:  =B17*$B$2

Also, you can make your formula in column E more consistent by summing up an expanding range:
E17: =SUM($D$17:D17)

Regards,
-Glenn
0
 
Berry MetzgerLean process improvement consultantCommented:
@DEBROCKE


I believe your formulas in F17:F22 are incorrect. The first two formulas are identical as follows...
 F17: =D17*VLOOKUP(E17,$A$5:$D$13,3,TRUE)
 F18: =D17*VLOOKUP(E17,$A$5:$D$13,3,TRUE)  where it should be: =D17*VLOOKUP(E18,$A$5:$D$13,3,TRUE)
 ...and so the one-row reference mistake continues for the remainder of the range.
 Berry
0
All Courses

From novice to tech pro — start learning today.