After reading this article you will be able to Average numbers if there is an error in the column.
In case you have column where some cells are filled with numbers and 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 is at least three consecutive number in a column then gives Average quantity otherwise show Error 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 check the criteria then count (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 show Error (column D).
Syntax: = AVERAGE(number1, [number2], …)
Result: 7 ‘Average if three consecutive numbers
It returns Average in the given range (C3 to C5)