How to Use Array Formulas to Ignore Errors in Excel

To find the average value for a range containing error values—such as #DIV/0!, or #NAME?—use the AVERAGE, IF, and ISNUMBER functions together in an array formula. Sometimes, such errors are generated in an incomplete worksheet, and these errors will be eliminated at a later time by the addition of new data. If you need to find the average value for the existing data, you can use the AVERAGE function along with the IF and ISNUMBER functions in an array formula to give you the average while ignoring the errors.

Find the Average in a Range

Note: the formula below can only be used with a contiguous range.

The example below uses the following array formula to find the average for the range D1 to D4.

• =AVERAGE(IF(ISNUMBER(D1:D4),D1:D4))

In this formula,

• ISNUMBER: test to see if all data in the range D1:D4 are numbers—returns TRUE or FALSE only
• IF: includes those values that are numbers in the range to be averaged
• AVERAGE: finds the average value (arithmetic mean) for all numbers in the range D1 to D4

CSE Formulas

Normally, ISNUMBER only tests one cell at a time. To get around this limitation, a CSE or array formula is used, which results in the formula evaluating each cell in the range D1 to D4 separately to see if it meets the condition of containing a number. 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. Because of the keys pressed to create the array formula, they are sometimes referred to as CSE formulas.

AVERAGE IF Array Formula

1. Enter the following data into cells D1 to D4: 10, #NAME?, 30, #DIV/0!
   1. 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.
   2. 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.
2. Click on cell E1 - the location where the formula results will be displayed
3. Type the following:= AVERAGE (IF (ISNUMBER (D1:D4), D1:D4 ) )

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 20 should appear in cell E1 since this is the average for the two numbers in the range 10 and 30
4. By clicking on cell E1, the complete array formula{ = AVERAGE (IF (ISNUMBER (D1:D4), D1:D4 ) ) }
   1. can be seen in the formula bar above the worksheet

Substituting MAX, MIN, or MEDIAN for AVERAGE

Because of the similarity in syntax between the AVERAGE function and other statistical functions, such as MAX, MIN, and MEDIAN, these functions can be substituted into the AVERAGE IF array formula above to obtain different results.

To find the largest number in the range,

• = MAX (IF (ISNUMBER (D1:D4), D1:D4 ) )
  • To find the smallest number in the range,
• = MIN (IF (ISNUMBER (D1:D4), D1:D4 ) )
  • To find the median value in the range,
• = MEDIAN (IF (ISNUMBER (D1:D4), D1:D4 ) )

​As with the AVERAGE IF formula, the above three formulas must also be entered as array formulas.