If AVERAGE or SUM refer to cells that contain #VALUE! errors, the formulas will result in a #VALUE! error.

In order to overlook the error values, we’ll construct a formula that ignores the errors in the reference range while calculating the average with the remaining “normal” values.

To work around this scenario, we use a combination of AVERAGE along with IF and ISERROR to determine if there is an error in the specified range. This particular scenario requires an array formula:

=AVERAGE(IF(ISERROR(B2:D2),"",B2:D2))

**Note:** This is an Array formula and needs to be entered with CTRL+SHIFT+ENTER. Excel will automatically wrap the formula in braces {}. If you try to enter them yourself Excel will display the formula as text.

**Note:** The above function will not only work for #VALUE!, but also for #N/A, #NULL, #DIV/0!, and others.

You could also use SUM in the same fashion:

=SUM(IF(ISERROR(B2:D2),"",B2:D2))

## Do you have a specific function question?

## Help us improve Excel

Do you have suggestions about how we can improve the next version of Excel? If so, please check out the topics at Excel User Voice.