Table of Contents
If you want to ignore #N/A values when using formulas in Excel, you can use the IFERROR function to check for errors and return a default value if an error is encountered. This can be used with any formula to prevent the #N/A error from affecting the results of the formula. To do this, you just need to wrap the formula in the IFERROR function, and specify the value to return if an error is encountered.
You can use the following basic syntax to calculate the mean, median, sum, standard deviation, etc. in Excel while ignoring #N/A values:
=AVERAGE(IFNA(A2:A21, "")) =MEDIAN(IFNA(A2:A21, "")) =SUM(IFNA(A2:A21, "")) =STDEV(IFNA(A2:A21, ""))
This syntax simply replaces #N/A values with blanks and then calculates the descriptive statistic you’re interested in.
The following examples show how to use this syntax in practice.
Example 1: Calculate Mean & Ignore #N/A Values
The following screenshot shows how to calculate the mean of a dataset that contains #N/A values:
The mean of the dataset (ignoring all #N/A values) is 14.76.
Example 2: Calculate Median & Ignore #N/A Values
The following screenshot shows how to calculate the median of a dataset that contains #N/A values:
The median of the dataset (ignoring all #N/A values) is 14.
Example 3: Calculate Sum & Ignore #N/A Values
The following screenshot shows how to calculate the sum of a dataset that contains #N/A values:
The sum of the dataset (ignoring all #N/A values) is 251.
Example 4: Calculate Standard Deviation & Ignore #N/A Values
The standard deviation of the dataset (ignoring all #N/A values) is 6.