Software & Apps MS Office How to Use Array Formulas to Ignore Errors in Excel by Ted French Writer Former Lifewire writer Ted French is a Microsoft Certified Professional who teaches and writes about spreadsheets and spreadsheet programs. our editorial process Ted French Updated on June 16, 2019 Tweet Share Email Hero Images /Getty Images MS Office Excel Word Powerpoint Outlook 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 onlyIF: includes those values that are numbers in the range to be averagedAVERAGE: 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 Enter the following data into cells D1 to D4: 10, #NAME?, 30, #DIV/0!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 E1 - the location where the formula results will be displayedType the following:= AVERAGE (IF (ISNUMBER (D1:D4), D1:D4 ) ) Creating the Array Formula Press and hold down the Ctrl and Shift keys on the keyboardPress the Enter key on the keyboard to create the array formulaThe answer 20 should appear in cell E1 since this is the average for the two numbers in the range 10 and 30By clicking on cell E1, the complete array formula{ = AVERAGE (IF (ISNUMBER (D1:D4), D1:D4 ) ) }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. Was this page helpful? Thanks for letting us know! Get the Latest Tech News Delivered Every Day Email Address Sign up There was an error. Please try again. You're in! Thanks for signing up. There was an error. Please try again. Thank you for signing up. Tell us why! Other Not enough details Hard to understand Submit