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
ADJ-adminAsked:
Who is Participating?
 
Glenn RayConnect With a Mentor 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
 
nutschConnect With a Mentor 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
 
nutschCommented:
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Glenn RayExcel 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
 
Glenn RayExcel 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
 
nutschCommented:
From what I understand, commissions from 1 to 7 should be $175

ADJ-admin, can you confirm?
0
 
SANTABABYCommented:
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
 
SANTABABYCommented:
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
 
SANTABABYCommented:
Sorry again(I really am):
The formula goes to G2 and then copy to other G cells.
0
 
Glenn RayExcel VBA DeveloperCommented:
ADJ-admin...could you please follow up and
1) answer nutsch's question on the quota, and
2) close the thread if answered?

Thanks!
-Glenn
0
 
ADJ-adminAuthor 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.

All Courses

From novice to tech pro — start learning today.