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: Simple formula. But what happens when the credit is greater than the total? If that happens, you’ll see a negative balance:
A negative balance doesn’t make sense in this case, so you reach for the handy IF function:
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:
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:
However, with the MIN function, you can write a compact formula that fully captures the requirement:
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:
Force negative numbers to zero Cap percentage at specific amount
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.
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.
