Using MIN/MAX to answer whichever lower/higher questions

What is “Whichever higher”?

Situation: You call to your favourite restaurant to reserve a private room for family dinner on Saturday.  The restaurant manager tells you that there will be a minimum charge of $2000 for a private room.   That mean, if your orders exceed the minimum charge, you will pay for what you order.  On the other hand, if your orders just $1500 values of food, you still need to pay $2000.

Put it in other words…

IF $Order > Minimum Charge, Then $Order, Else Minimum Charge

In Excel, the top-of-mind function should be IF:

A1 = $Order
B1 = Minimum Charge

Indeed, the same can be achieved by using MAX:


Isn’t it a nice alternative?

What is “Whichever lower”?

Situation: You want to subscribe a mobile data plan.  However, you are worry that you are spending too much than you can budget for.  Luckily, most mobile vendors offer what they called “Hassle-Free” data plan:  No matter how much data you use, your bill payment will be capped at a fixed amount, say $1000, i.e. a maximum payment.

Put it in other words…

IF $ Data used < Maximum Payment, Then $Data usage, Else Maximum Payment

In Excel, it is

A1 = $ Data used
B1 = Maximum Payment

This time, the same can be achieved by using MIN:


Shorter and easier expression!  Isn’t it?

The following screenshot demonstrates the usage:

Excel Tip - Alternative to IF

This is cool…. BUT I am a bit confused because…

  • When the situation is dealing with Minimum Payment, MAX is used
  • When the situation is dealing with Maximum Payment, MIN is used

Totally agree with you… as I am confused too  😛

So let’s put it in other words…

  • When dealing with “Whichever higher“, use MAX
  • When dealing with “Whichever lower“, use MINExcel Tip - Alternative to IF 1


Make more sense now?  I hope so. 🙂


About MF

An Excel nerd who just transition into a role related to data analytics at current company......😊 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in Formula and tagged , , . Bookmark the permalink.

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.