How to Ignore #N/A Error When Getting Average in Excel

Get FREE Advanced Excel Exercises with Solutions!

Microsoft Excel offers lots of functions, one of which is the AVERAGE function. This computes the average of a value in a range of cells. However, there are instances in which a range of cells are empty or could carry zeroes, which could have an impact on the outcome. When we use this function to compute the average, this returns the #N/A error. The error code #N/A indicates that no value is available. In this article, we will demonstrate some effective ways to ignore the NA (#N/A) error when getting an average in Excel.


How to Ignore #N/A Error When Getting Average in Excel: 5 Effective Ways

The AVERAGE function is classified under Excel Statistical functions. The function employs the mean or average of a set of inputs. But sometimes, we will receive an error message if we use the AVERAGE function to average a group of cells that includes some wrong values. Let’s follow the ways to ignore the NA (#N/A) error when getting an average in Excel.


1. Ignore #N/A Error with AVERAGEIF Function in Excel

To overlook the #N/A error while calculating the average of a group of cells with some null value, we are going to use the AVERAGEIF function. This function computes the average of a value in a range of cells as well as the average of cells based on specific conditions. The syntax of the function is:

AVERAGEIF (range, criteria, [average_range])

Let’s see the steps down to use this function to ignore the error.

STEPS:

  • Firstly, we need to create a dataset. Suppose, we have some students’ names and their marks for a specific subject. Now, we want to compute the average of those students’ marks. Some of the students were absent during the exam period that’s why they have the #N/A value in their mark column.

excel average ignore na

  • As we know, to calculate the average we all use the average function.
  • So, we select cell C12 and insert the formula to see the result.
=AVERAGE(C5:C10)
  • Press Enter.
  • Unfortunately, we will get an error which is the #N/A error.

  • To avoid this, we are using the AVERAGEIF function. So, choose cell C12 and insert the formula.
=AVERAGEIF(C5:C10, ">=0")
  • Then, hit the Enter button.
  • You will get the accurate average value without any error.

Read More: [Fixed!] AVERAGE Formula Not Working in Excel


2. Use Excel AGGREGATE Function for Ignoring #N/A Error Values

Another way to ignore the NA (#N/A) error while getting the average is by using the AGGREGATE function. When using the AGGREGATE function, we are able to neglect error values while applying various aggregate functions to a range of data.

AGGREGATE(function_num, options, ref1, [ref2], …)

Let’s look at the procedures to use this function.

STEPS:

  • To use the Excel AVERAGE function, we are using the same dataset as the previous method.
  • Select cell C12 and put the formula using the function.
=AVERAGE(C5:C10)
  • Then, press Enter.

excel average ignore na

We encounter an error of #N/A.

  • We are using the AGGREGATE function to prevent this. Therefore, choose cell C12 and enter the formula below.
=AGGREGATE(1,6,C5:C10)

Read More: How to Fix Divide by Zero Error for Average Calculation in Excel


3. Combine IFERROR and AVERAGEIF Functions to Avoid Error When Getting Average

We combine the IFERROR & AVERAGEIF functions to ignore the NA (#N/A) error in Excel. Microsoft Excel’s IFERROR function produces a different value if a formula fails. The syntax of the IFERROR function is:

IFERROR( formula, alternate_value )

Let’s go through the steps down.

STEPS:

  • Let’s say we know the names of certain students as well as their grades for a particular subject. Now, we want to figure out what those students’ average grades are. Some students missed the exam session, which is why their mark column has a #N/A value.

  • We all use the AVERAGE function to determine the average since we are aware of this.
  • We thus choose cell C12 to display the outcome in.
=AVERAGE(D5:D10)
  • Press Enter.
  • Unfortunately, we will encounter an error of #N/A.

  • Using the combination of the IFERROR & AVERAGE functions, we are able to prevent this. Choose cell C12 and enter the formula in that cell.
=IFERROR(AVERAGEIF($D5:$D10,"<>#N/A"),"")
  • After that, press the Enter key.
  • Finally, you will have an accurate average number.

excel average ignore na


4. Merge AVERAGE and IFNA Functions to Ignore #N/A Error in Excel

In cases where a formula produces the #N/A error value, the IFNA function shows the value you provide. In all other cases, it gives an output. As we want to get the average we merge the AVERAGE & IFNA functions to avoid the error.

STEPS:

  • Likewise the previous method, we are going to use the same dataset.
  • Now, select the cell C12 and write down the formula there.
=AVERAGE(C5:C10)
  • Hit Enter.
  • But we get an error.

  • We are applying the AVERAGE & IFNA functions to prevent this. Therefore, choose cell C12 and enter the formula there.
=AVERAGE(IFNA(D5:D10, ""))
  • Then, press the Enter key.
  • In the end, you will obtain the precise average.


5. Combination of AVERAGE, IF & ISERROR Functions to Skip #N/A Error for Getting Average Value

If we use the combination of AVERAGE, IF & ISERROR functions, we can ignore the #N/A error. The IF function is employed to construct logical comparisons between a value. The logical function ISERROR is used to determine whether or not the cells are referred to contain errors.

STEPS:

  • To begin with, we’ll use the same dataset that we did with the prior approach.
  • Next, select the cell C12 and enter the formula there at this time.
=AVERAGE(C5:C10)
  • Subsequently, hit the Enter key.
  • But an error occurs.

excel average ignore na

  • To avoid this, we are using the combination of AVERAGE, IF & ISERROR functions. Choose cell C12 and type the formula there as a result.
=AVERAGE(IF(ISERROR(D5:D10),"",D5:D10))
  • Press the Enter key after that.
  • You will ultimately arrive at an accurate average.


Things to Keep in Mind

  • While inputting any function, the function name must be enclosed in empty brackets. If not, Excel will fail to identify it as a function.
  • Alternatively, we can enter the value #N/A into a cell. For compatibility with other spreadsheet programs.

Download Practice Workbook

You can download the workbook and practice with them.


Conclusion

The above ways will assist you to ignore the NA (#N/A) error when getting an average in Excel. Hope this will help you! Please let us know in the comment section if you have any questions, suggestions, or feedback.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Sabrina Ayon
Sabrina Ayon

Sabrina Ayon, a Computer Science and Engineering graduate from United International University, has been an integral part of the ExcelDemy project for two years. She authored 150+ articles, excelling in instructing through visually engaging Excel tutorials. With a passion for teaching, Sabrina conducted sessions on Excel VBA, sharing her knowledge and insights with others. Currently holding the position of Project Manager for the ExcelDemy Visual Development Project, she oversees various aspects of the project, ensuring its smooth operation... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo