Where there’s a MIN there’s a MAX

It's an odd one, but wherever you need to apply a minimum in Excel, you'll often find yourself using the MAX formula.

Let's say you need to charge for transactions (stored in cell A1) at a rate of £20 each, subject to a minimum of £3,000, then your formula will read:

=MAX(3000,A1*20)

In this event, if you only have a few transactions (let's say seven), then this will give you the maximum of 3,000 and 140, thus charging the £3,000 minimum.

If you have lots of transactions (237, say), then this will give you the maximum of 3,000 and 4,740, thus charging the transaction fee of £4,740, as it's surpassed the minimum.

So where you see the word "minimum", you might be wise to use the MAX formula.

• Tweet