Find the Largest Negative or Positive Number in Excel

Sometimes, rather than just find the largest or maximum number for all of your data; you need to find the largest number in a subset - such as the largest positive or negative number.

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:

=MAX( IF( A1:B5>0, A1:B5 ))

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:

{=MAX( IF( A1:B5>0, A1:B5 ) ) }

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

1. Enter the numbers seen in the image above into cells A1 to B5 of a worksheet
2. In cells A6 and A7 type the labels Max Positive and Max Negative

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 displayed
• Type the following:

=MAX( IF ( A1:B5>0, A1:B5 ) )

Creating the Array Formula

1. Press and hold down the Ctrl and Shift keys on the keyboard
2. Press the Enter key on the keyboard to create the array formula
3. The answer 45 should appear in cell B6 since this is the largest positive number in the list

If You Click on Cell B6, the Complete Array Formula can be seen in the formula bar above the worksheet

{ = MAX( IF( A1:B5>0, A1:B5 ) ) }

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 B7
• Type the following:

=MAX( IF ( A1:B5<0, A1:B5 ) )

• Follow the steps above to create the array formula
• The 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.