After reading this article you will be able to Average numbers if there is an error in the column.
In case you have a column where some cells are filled with numbers and the remaining are #NA (not available). If you want to find average when it met criteria otherwise show an Error.
We have two columns Date and Quantity. Our task is to count and if there are at least three integers in a column then give Average otherwise show “Error” as text.
Formula to implement:
Download Your Example Excel File
Formula Explanation (step by step)
Let’s break the formula into multiple steps to understand its application.
Syntax: =COUNTIF(range, criteria)
It first checks the criteria then counts (C3 to C5) as no number in the given range is matched criteria (NA()=0) so it returns 0.
Syntax: IF(logical_test, value_if_true, [value_if_false])
As 0=0 so, if there are three consecutive numbers in a column (column C) is TRUE then returns Average otherwise it returns FALSE and shows Error (column D).
Syntax: = AVERAGE(number1, [number2], …)
Result: 7 ‘Average if three consecutive numbers
It returns Average in the given range (C3 to C5)