If maximum number is greater than

This tutorial shows how to test if the largest number from a range of cells is greater than a specific number and return a value if the test is True or False through the use of an Excel formula, with the IF and MAX functions

Excel

If maximum number is greater than using IF and MAX functions

EXCEL FORMULA 1. If maximum number is greater than

EXCEL

Hard coded formula

Cell reference formula

=IF(MAX(B5:B9)>300,"Include","Exclude")

=IF(MAX(B10:B14)>C5,C6,C7)

GENERIC FORMULA

=IF(MAX(range)>number,value_if_true,value_if_false)

ARGUMENTS
number: A number that you want to test a range against for being greater than the largest number in that range.
range: A range that contains the numbers in which you want to identify the maximum number.
value_if_true: Value to be returned if the largest number in the range that is being tested is greater than a specified number.
value_if_false: Value to be returned if the largest number in the range that is being tested is less than or equal to a specified number.

GENERIC FORMULA

=IF(MAX(range)>number,value_if_true,value_if_false)

EXPLANATION

This formula uses the IF and MAX functions to test if the largest number in a range is greater than a specific number and return a value if the test is True or False.

Click on either the Hard Coded or Cell Reference button to view the formula that has the number that you want to test the range against for being greater than the largest number in the range and the return values directly entered into the formula or referenced to specific cells.

In this example the formula initially identifies the largest number in range B5 to B9 (hard coded example) or B10 to B14 (cell reference example) by using the MAX function. This will return a value of 400, given this is the largest number in the selected range. Embedding the MAX function in the IF function the formula performs a test to identify if largest number in the range is greater than a specific number, which in this example is 300, and return a text value of "Include" otherwise it will return a text value of "Exclude". In this example given that the largest number in the range (400) is greater than 300 the formula will return a text value of "Include".

If you are using the formula with values entered directly in it and want to return a numerical value, instead of a text value, you do not need to apply the double quotation marks around the values that are to be returned e.g. (=IF(C5="",1,0)).

Related Functions	Description	Related Functions and Description
IF Function	The Excel IF function performs a test on specified conditions entered into the formula and returns a specified value if the result is TRUE or another specified value if the result is FALSE
MAX Function	The Excel MAX function returns the largest value from a specified range of numeric values