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:
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:
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.
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.