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 following screenshot shows how to calculate the standard deviation of a dataset that contains #N/A values:
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