For average you would type:
NB: In this formula Option 1 is to calculate average while Option 6 is to ignore error values.
When you apply the AVERAGE function to calculate the average of a range of cells which including some error values, you will get an error result. This following article will talk about how to average cells ignoring errors in Excel.
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 71% of your time and solve 82% Excel problems for you. 300+ advanced tools designed for 1500+ work scenario, make Excel much easy and increase productivity immediately.60-day Unlimited Free Trial
The following handy array formulas can help you to calculate the average of the cells excluding the errors. Please do as this:
1. Enter this array formula: =AVERAGE(IF(ISERROR(A1:C6),"",A1:C6)), see screenshot:
2. Then press Ctrl + Shift + Enter keys simultaneously, and you will get the average result as below screenshot shown:
1. Except the above formula, here is another formula also can help you: =AVERAGE(IF(ISNUMBER(A1:C6),A1:C6)), please remember to press Ctrl + Shift + Enter keys.
2. In the above formulas, A1:C6 is the data range that you want to calculate, you can change it as your needing.