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:

=IF(A1>B1,A1,B1)
where
A1 = $Order
B1 = Minimum Charge

Indeed, the same can be achieved by using MAX:

=MAX(A1,B1)

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

=IF(A1<B1,A1,B1)
where 
A1 = $ Data used
B1 = Maximum Payment

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

=MIN(A1,B1)

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

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

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s