# Need formula for excel

See attached, need column G to calculate commission based on guidelines on right of the chart once you open it.

Commision-Formula.xlsx
###### Who is Participating?

Excel VBA DeveloperCommented:
Insert the following formula in cell G5 and copy down.
=IF(AND(D5>=E5,E5<>""),IF(D5>=13,175+400+((D5-12)*150),IF(D5>=10,275+(D5-9)*100,175+(D5-7)*50)))

I've attached a modified version of the file. Commision-Formula.xlsx
0

Commented:
With a small change in your productivity bonus table, I have a formula that can be expanded if the structure changes

=(D5>0)*MAX(175,SUM(IF((I18-\$K\$3:\$N\$3)>0,(1+I18-\$K\$3:\$N\$3)*\$K\$4:\$N\$4,0))-SUM(IF(I18>\$K\$3:\$N\$3,(1+I18-\$K\$3:\$N\$3)*\$J\$4:\$M\$4,0)))

entered with Ctrl+Shift+ENter as it is an array formula.

See attached file.
Copy-of-Commision-Formula-1.xlsx
0

Commented:
Oops, formula copy error. Here is what it should be

=(D5>0)*MAX(175,SUM(IF((D5-\$K\$3:\$N\$3)>0,(1+D5-\$K\$3:\$N\$3)*\$K\$4:\$N\$4,0))-SUM(IF(D5>\$K\$3:\$N\$3,(1+D5-\$K\$3:\$N\$3)*\$J\$4:\$M\$4,0)))

THomas
Copy-of-Commision-Formula-1.xlsx
0

Excel VBA DeveloperCommented:
nutsch's answer is ideal (using an array formula to allow expansion), but needs to be modified to satisfy the minimum MID quota of 8.  It currently returns commissions if the MID count is below the quota (8).  Also, the commission calcuation for MID=8 should be \$225; the formula returns \$200.

Here is his modified formula (again, you must enter [Ctrl]+[Shitf]+[Enter] for this)

=(D5>=E8)*MAX(225,SUM(IF((D5-\$K\$3:\$N\$3)>0,(1+D5-\$K\$3:\$N\$3)*\$K\$4:\$N\$4,0))-SUM(IF(D5>\$K\$3:\$N\$3,(1+D5-\$K\$3:\$N\$3)*\$J\$4:\$M\$4,0)))

0

Excel VBA DeveloperCommented:
I forgot to add, the above formula works if you change the quota as well.  See attached workbook for a modified version of nutsch's array solution with the modified formula. Commision-Formula-array-solution.xlsx Commision-Formula-array-solution.xlsx
0

Commented:
From what I understand, commissions from 1 to 7 should be \$175

0

Commented:
Try:
=IF(D5>=8,175+MIN(MAX(D5-7,0),2)*50+MIN(MAX(D5-9,0),3)*100+MAX(D5-12,0)*150,0)
0

Commented:
Contd...
Use:
=IF(D5>=8,175+MIN(MAX(D5-7,0),2)*50+MIN(MAX(D5-9,0),3)*100+MAX(D5-12,0)*150,0)
For the cell D5 and then copy to other cells D2...D?
0

Commented:
Sorry again(I really am):
The formula goes to G2 and then copy to other G cells.
0

Excel VBA DeveloperCommented:
1) answer nutsch's question on the quota, and

Thanks!
-Glenn
0

Author Commented:
Thank you very much!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.