How to Ignore #N/A Values When Using Formulas in Excel


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:

Excel average and ignore NA

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:

Median in Excel and ignore NA

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:

SUM in Excel and ignore NA

The sum of the dataset (ignoring all #N/A values) is 251.

Example 4: Calculate Standard Deviation & Ignore #N/A Values

The following screenshot shows how to calculate the standard deviation of a dataset that contains #N/A values:

Standard deviation and ignore NA in Excel

The standard deviation of the dataset (ignoring all #N/A values) is 6.

Additional Resources

The following tutorials explain how to perform other common tasks in Excel:

How to Replace #N/A Values in Excel
How to Easily Find Outliers in Excel
How to Find the Top 10% of Values in an Excel Column

Leave a Reply

Your email address will not be published.