*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**.

**QUERY**

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

**Functions applicable:**

We will be creating a COUNTIIF and IF along with AVERAGE formula in Excel:

**Formula Explanation (step by step)**

Use shortcut** F9** function key, tab clicks on **evaluate the formula****.**

Let’s break the formula into **multiple steps** to understand its application.

First formula

**Syntax:** =COUNTIF(range, criteria)

Step 1:

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.

Second formula:

IF(COUNTIF(C3:C5,NA())=0,AVERAGE(C3:C5),”Error”)

**Syntax:** IF(logical_test, value_if_true, [value_if_false])

Step 2:

Result: **TRUE**

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)*.

Third formula:

AVERAGE(C3:C5)

**Syntax:** = AVERAGE(number1, [number2], …)

Step 3:

**Result:** 7 ‘Average if three consecutive numbers

It returns Average in the given range (C3 to C5)