If the amount of data is small, the task might be easy to accomplish by manually selecting the correct range for the MAX function. In other circumstances, such as a large unsorted data sample, selecting the range correctly could prove to be difficult if not impossible. By combining the IF function with the MAX in an array formula, conditions - such as positive or negative numbers only - can easily be set so that only the data matching these parameters is tested by the formula.
MAX IF Array Formula Breakdown
The formula used in this tutorial to find the largest positive number is: The job of each part of the formula is:
The IF function filters the data so that only those numbers that meet the chosen criterion are passed on to the MAX function the MAX function finds the highest value for the filtered data The array formula - indicated by the curly braces { } surrounding the formula - allows the IF function’s logical test argument to search the entire range of data for a match - such as numbers greater than zero - rather than just a single cell of data
CSE Formulas
Array formulas are created by pressing the Ctrl, Shift, and Enter keys on the keyboard at the same time once the formula has been typed in. The result is that the entire formula - including the equal sign - is surrounded by curly braces. An example would be: Because of the keys pressed to create the array formula, they are sometimes referred to as CSE formulas.
Excel’s MAX IF Array Formula Example
As seen in the image above, this tutorial example uses the MAX IF array formula to find the largest positive and negative values in a range of numbers. The steps below first create the formula to find the largest positive number followed by the steps needed to find the largest negative number.
Entering the Tutorial Data
Entering the MAX IF Nested Formula
Since we are creating both a nested formula and an array formula, we will need to type the entire formula into a single worksheet cell. Once you have entered the formula DO NOT press the Enter key on the keyboard or click on a different cell with the mouse as we need to turn the formula into an array formula.
Click on cell B6 - the location where the first formula results will be displayedType the following:
Creating the Array Formula
If You Click on Cell B6, the Complete Array Formula can be seen in the formula bar above the worksheet
Finding the Largest Negative Number
The formula to find the largest negative number differs from the first formula only in the comparison operator used in the IF function’s logical test argument. Since the objective is to now find the largest negative number, the second formula uses the less than operator ( < ), rather than the greater than operator ( > ), to test only the data that is less than zero.
Click on cell B7Type the following:
Follow the steps above to create the array formulaThe answer -8 should appear in cell B7 as this is the largest negative number in the list
Getting #VALUE! for an Answer
If cells B6 and B7 display the #VALUE! error value rather than the answers indicated above, it is probably because the array formula was not created correctly. To correct this problem, click on the formula in the formula bar and press the Ctrl, Shift and Enter keys on the keyboard again.
