[2 days left] Whatâ€™s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
Solved

Need formula for excel

Posted on 2011-09-15
Medium Priority
278 Views
See attached, need column G to calculate commission based on guidelines on right of the chart once you open it.

Commision-Formula.xlsx
0
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 4
• 3
• 3
• +1

LVL 27

Accepted Solution

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

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

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

LVL 27

Expert Comment

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

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

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

0

LVL 10

Expert Comment

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

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

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

LVL 27

Expert Comment

ID: 36560756
1) answer nutsch's question on the quota, and

Thanks!
-Glenn
0

Author Closing Comment

ID: 36561040
Thank you very much!
0

Featured Post

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are â€¦
My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a qâ€¦
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. â€¦
Suggested Courses
Course of the Month14 days, 10 hours left to enroll