Summary

In this article, we show you how to replace a complicated IF statement with a clever and compact formula based on MIN or MAX. This is a great tip any time you need to choose the smaller or greater of two values inside a formula.

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 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 video courses to help you learn quickly in a more structured environment.

Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.