Excel Magic Trick 100: Instead of Long IF, Use MEDAIN function Commission W Hurdle Calculation

Formula from Mr Excel Challenge at Mr Excel web site. You will not believe your eyes: See how to replace a complicated IF formula with the MEDIAN function, or a MIN MAX formula. See 3 amazing examples.

When you have a hurdle such a Commission Hurdle or a Payroll Taxable Earning Hurdle, and you are using an IF function formula, it may be easier to use the MEDAIN function in your formula.

See a Payroll example, a Sales Commission Example and an extra-credit homework example.

Category:

Science & Technology

Tags:

• Excel
• IF
• MEDIAN
• Min
• MAX
• function
• formula
• Payroll
• Commission
• Sales
• Mike
• Gel
• Girvin
• Highline
• Community
• College
• MrExcel
• Challenge
• at
• web
• site

[ExcelIsFun]

Try THE best Excel question site:

mrexcel[dot]com/forum

[Dubz229]

Hi Mr. Excel, I'm having trouble creating a decision model using the MIN,MAX,IF or even the AND function. Simplified, I'm trying to find a way to consider the constraints of how much we have ordered AND the demand level e.g.:

a) week1: OrderQTY = 30, Demand = 10 = sales of 10*price

b) week2: OrderQTY=15, Demand = 20 = sales of 15*price

c) week 3: OrderQTY=20, demand=20 = sales of 20*price

Please help! Thanks!!!!!!!

[ExcelIsFun]

That is awesome!!! I love the period trcik. Thanks!

[kelch12]

Actually, I thought this video was about an unknown "MEDAIN" function in Excel. Then I found out that clever ExcelisFun TRICKED me into another Excel-lent use of "MEDIAN" video.

@11:10 right after typing "B7", instead of typing ":" (which involves pressing two keys) you may simply press "." (period) and achieve the same effect.