asked on

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

Professor J

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

DEBROCKE

ASKER

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

ASKER CERTIFIED SOLUTION

Glenn Ray

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

DEBROCKE

ASKER

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.

Glenn Ray

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

Berry Metzger

@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