ADJ-admin
asked on
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
Commision-Formula.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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+D 5-$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)))
Here is his modified formula (again, you must enter [Ctrl]+[Shitf]+[Enter] for this)
=(D5>=E8)*MAX(225,SUM(IF((
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
From what I understand, commissions from 1 to 7 should be $175
ADJ-admin, can you confirm?
ADJ-admin, can you confirm?
Try:
=IF(D5>=8,175+MIN(MAX(D5-7 ,0),2)*50+ MIN(MAX(D5 -9,0),3)*1 00+MAX(D5- 12,0)*150, 0)
=IF(D5>=8,175+MIN(MAX(D5-7
Contd...
Use:
=IF(D5>=8,175+MIN(MAX(D5-7 ,0),2)*50+ MIN(MAX(D5 -9,0),3)*1 00+MAX(D5- 12,0)*150, 0)
For the cell D5 and then copy to other cells D2...D?
Use:
=IF(D5>=8,175+MIN(MAX(D5-7
For the cell D5 and then copy to other cells D2...D?
Sorry again(I really am):
The formula goes to G2 and then copy to other G cells.
The formula goes to G2 and then copy to other G cells.
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
1) answer nutsch's question on the quota, and
2) close the thread if answered?
Thanks!
-Glenn
ASKER
Thank you very much!
=(D5>0)*MAX(175,SUM(IF((D5
THomas
Copy-of-Commision-Formula-1.xlsx