Exceljet

Quick, clean, and to the point

Replace ugly IFs with MAX or MIN

by Dave Bruns | September 9, 2016

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:

balance=IF(total-credit>0,total-credit,0)

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:

=IF(1.5%*amount>3000,3000,1.5%*amount)

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:

=MIN(1.5%*amount,3000)

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.

More examples

Here are a couple more examples of using MAX or MIN to replace IF:

More formulas

Like so many things in Excel, the trick to learning more formulas is more exposure. To help you out, we maintain a large collection of sample formulas you can browse and study. This is a great way to find specific solutions to many problems you're likely to encounter in Excel. We also have a good library of videos courses to help you learn quickly in a more structured environment.

Your YouTube videos ROCK!! Thanks for producing them with such quality and brevity. -Mike
Excel foundational video course
Excel Pivot Table video training course
Excel conditional formatting video course
Excel formulas and functions video training course
Excel Shortcuts Video Course