Solved

Need formula for excel

Posted on 2011-09-15
11
269 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
[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
  • Learn & ask questions
  • 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
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…

729 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