?
Solved

Need formula for excel

Posted on 2011-09-15
11
Medium Priority
?
281 Views
Last Modified: 2012-05-12
See attached, need column G to calculate commission based on guidelines on right of the chart once you open it.

Commision-Formula.xlsx
0
Comment
Question by:ADJ-admin
  • 4
  • 3
  • 3
  • +1
11 Comments
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 200 total points
ID: 36545319
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
 
LVL 39

Assisted Solution

by:nutsch
nutsch earned 200 total points
ID: 36545693
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
 
LVL 39

Expert Comment

by:nutsch
ID: 36545714
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 27

Expert Comment

by:Glenn Ray
ID: 36546134
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
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 36546147
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
 
LVL 39

Expert Comment

by:nutsch
ID: 36546268
From what I understand, commissions from 1 to 7 should be $175

ADJ-admin, can you confirm?
0
 
LVL 10

Expert Comment

by:SANTABABY
ID: 36546421
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
 
LVL 10

Expert Comment

by:SANTABABY
ID: 36546423
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
 
LVL 10

Expert Comment

by:SANTABABY
ID: 36546432
Sorry again(I really am):
The formula goes to G2 and then copy to other G cells.
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 36560756
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
 

Author Closing Comment

by:ADJ-admin
ID: 36561040
Thank you very much!
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question