Solved

Need formula for excel

Posted on 2011-09-15
11
263 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
This article will show you how to use shortcut menus in the Access run-time environment.
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 …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

791 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