Using MIN or MAX as an alternative to IF statements in Excel
- Excel Navigator
- 5 days ago
- 3 min read
The MIN and MAX functions in Excel let you find the highest and lowest value in a dataset or select values.
The same output can be achieved using an IF statement, but this tends to result in a longer and more complex formula.
Seeing how the functions perform in the same scenario can help make it clear why, in certain circumstances, it can be better to use MIN or MAX over an IF statement.
Download File
If you would like to follow along please download the file below:
What are the MIN and MAX functions
The MIN function returns the minimum value in a range or set of values, and the MAX functions returns the maximum value in a range or set of values.
They both have the same syntax:
=MIN(number1,[number2],…)
=MAX((number1,[number2],…)
number1 - The value or range that you want to find the minimum value of. This can be a hard typed value, a single cell (e.g. A1) or a range of cells (e.g. A1:A10)
[number2] (Optional) - Like number1 this can be a hard typed value, a cell reference or a range, but it is optional.
If more than one value or range is entered in multiple number parameters, the functions will return the minimum or maximum number across all parameters.
Finding the minimum or maximum value in a range
Only the first part of both functions is compulsory, so if you have your data next to each other in a range of cells for the number1 parameter you can just select the range, and the function will return either than minimum or maximum number in that range.
If there is a blank value in the range you select the function will ignore it and still return the highest or lowest value.
Finding the minimum or maximum value across multiple parameters
If your data is not in one continuous set then you can use the optional parameters to add additional ranges.
IF statement
An IF statements allow you to enter a logical argument, and if its true return one outcome and if its false return another.
The syntax for an IF statement is
=IF(logical_test,[value_if_true],[value_if_false])
logical_test - An argument you want to test. It must objectively return a true or false value e.g. is one number greater than another, does one value equal another value
value_if_true – The output you want if the logical test is true. If left blank it will return TRUE
value_if_false - The output you want if the logical test is false. If left blank it will return FALSE
Using IF to find a minimum value
If you were trying to work out a bonus for staff who are paid 10% of sales up to a maximum bonus on £5,000 you could use an IF statement by creating an argument in the logical_test parameter.
Using MIN to calculate bonus
The same outcome can be achieved using MIN
If an employee’s bonus is calculated as 10% of their total sales but if that amount exceeds £5,000 then the bonus is capped at £5,000, the bonus needs to be the minimum of 10% of sales or £5,000.
Function comparison
One of the main benefits of the simplicity of MIN and MAX is that they can be easier to read if you are building a complex spreadsheet, especially a financial model where there is a best practice known as the “rule of thumb” which states that formulas should be kept as short and simple as possible e.g. no longer than your thumb.
Viewing the functions side by side from the bonus calculation example you can see how, while both return the same output, the MIN function is shorter and easier to read than the IF statement.
This is why MIN and MAX tend to be used in financial models as they comply with the “rule of thumb” best practice.
Conclusion
Both MIN and MAX are fairly simple formulas to use that can help quickly identify minimum and maximum values in data sets, whether they are continuous or spread across multiple arrays.
They can also be used as an alternative to IF statements if you are trying to make a formula shorter and more readable.
Komentar