Replace ugly IFs with MAX or MIN - KING OF EXCEL

Sunday, July 12, 2020

Replace ugly IFs with MAX or MIN

Replace ugly IFs with MAX or MIN

In this article, I want to show you how you can sometimes replace a more complicated IF formula with a more elegant MIN or MAX formula.
This is a very simple tip that really demonstrates how you can leverage Excel's formulas to create clever and compact solutions to everyday problems.
To illustrate, let's look at two examples.

A free lunch with MAX

Let's say you have a $50 credit at a restaurant. It's a one-time use credit that expires tomorrow, so you take your friend to dinner today. You split a salad, a pizza, and a couple of beers. When it comes time to apply the credit to the bill, you might calculate the balance like this:
balance= total-credit
Simple formula. But what happens when the credit is greater than the total?
If that happens, you'll see a negative balance:
Balance is negative when credit > total
A negative balance doesn't make sense in this case, so you reach for the handy IF function:
Typical IF formula to trap a negative balance
Problem solved. The IF function now catches negative results and returns zero instead.
This works, but the approach is ugly and redundant. The IF function is only there to trap negative results, and it forces you to repeat the main operation twice. There must be a more direct approach?
Yes, indeed, with the MAX function.

MAX instead of IF

You might not think of the MAX function in a situation like this, because it seems geared toward large sets of data. That's true, but MAX works equally well with small, even tiny, sets of data. 
Consider that you want the formula to return the greater of two things: the calculated balance, or zero. That means you can write a formula like this:
=MAX(total-credit, 0)
MAX returns a positive balance, or zero
Now MAX simply returns the greater of the two options — a positive balance or zero . Negative values are banished, and never make it to the final result.
Pretty cool, huh?

A capped fee with MIN

You can can use the MIN function in the same way. For example, assume you need to calculate an association fee of 1.5%, up to a maximum of $3,000?  In other words, use 1.5% to calculate the fee, but cap the result at $3,000.
You could of course use IF like this:
Using the IF function to calculate a capped fee
However, with the MIN function, you can write a compact formula that fully captures the requirement:
Using the MIN function to calculate a capped fee
Now any fee under $3000 is returned as calculated, but the total fee is never greater than $3000.
#evba #etipfree #eama #kingexcel
📤How to Download ebooks:

Popular Posts