Solved

Need formula for excel

Posted on 2011-09-15
11
259 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 50 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 50 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

707 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now