New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Adding MAX to tiered formula

Formulas, Functions, Formatting, Charts, Data Analysis, etc.

Adding MAX to tiered formula

Postby nicolette126 » Tue May 17, 2016 1:32 pm

Hello,

I'm working on a tiered commission plan with a kicker paying an additional 1% if achieved and a negative 1% kicker if other conditions are not achieved. So far, this formula meets my needs; however, I need to add a MAX condition so that it is not possible to earn more than 8% of C3. Do I start at the beginning or build it into the kickers?

thanks!


=IF(AND(OR(C2>=20,C3>=10000),C3>=12001),C3*0.05,IF(AND(OR(C2>=20,C3>=10000),C3>=8001),C3*0.04,IF(AND(OR(C2>=20,C3>=10000),C3>=4001),C3*0.03,C3*0.02)))+IF(AND(OR(C2>=20,C3>=10000),D2>0),C3*(D2*0.01),0)+IF(D2=0,C3*-0.01)+IF(F2=0,C3*-0.01)
  • 0

nicolette126
Rookie
 
Posts: 4
Joined: May 17, 2016
Reputation: 0
Excel Version: Excel 2013

Re: Adding MAX to tiered formula

Postby nicolette126 » Tue May 17, 2016 4:00 pm

This is where I am at, I think it works; any better ideas?

=MIN((IF(AND(OR(C2>=20,C3>=10000),C3>=12001),C3*0.05,IF(AND(OR(C2>=20,C3>=10000),C3>=8001),C3*0.04,IF(AND(OR(C2>=20,C3>=10000),C3>=4001),C3*0.03,C3*0.02)))+IF(AND(OR(C2>=20,C3>=10000),D2>0),C3*(D2*0.01),0)+IF(D2=0,C3*-0.01)+IF(F2=0,C3*-0.01)),C3*0.08)
  • 0

nicolette126
Rookie
 
Posts: 4
Joined: May 17, 2016
Reputation: 0
Excel Version: Excel 2013

Re: Adding MAX to tiered formula

Postby ConneXionLost » Tue May 17, 2016 4:29 pm

How about this:

Code: Select all
=MIN(IF(AND(OR(C2>=20,C3>=10000)),LOOKUP(C3,{0,4001,8001,12001},{0.02,0.03,0.04,0.05}))*C3+IF(AND(OR(C2>=20,C3>=10000),D2>0),C3*(D2*0.01),0)+IF(D2=0,C3*-0.01,0)+IF(F2=0,C3*-0.01,0),C3*0.08)


Cheers,
  • 0

ConneXionLost
Regular
 
Posts: 68
Joined: May 9, 2013
Location: Canada
Reputation: 19
Excel Version: 2003, 2010

Re: Adding MAX to tiered formula

Postby nicolette126 » Tue May 17, 2016 4:39 pm

That works too and is cleaner. I'll have to spend some time getting familiar with the benefits of LOOKUP, I appreciate the response, thanks!
  • 0

nicolette126
Rookie
 
Posts: 4
Joined: May 17, 2016
Reputation: 0
Excel Version: Excel 2013

Re: Adding MAX to tiered formula

Postby ConneXionLost » Wed May 18, 2016 2:53 pm

You could clean it further if you created a lookup table of the values, then used VLOOKUP in the formula. It would also allow you to mass modify your formula output and see your set-point values more easily.

Cheers,
  • 0

ConneXionLost
Regular
 
Posts: 68
Joined: May 9, 2013
Location: Canada
Reputation: 19
Excel Version: 2003, 2010

Re: Adding MAX to tiered formula

Postby nicolette126 » Wed May 18, 2016 7:38 pm

Thank you, I appreciate it! I actually take this formula logic and integrate it into the SalesForce platform. But for my own development, I will try it. Thanks!
  • 0

nicolette126
Rookie
 
Posts: 4
Joined: May 17, 2016
Reputation: 0
Excel Version: Excel 2013


Return to General Excel Questions

Who is online

Users browsing this forum: No registered users and 72 guests