Calculating average and standard deviation are often necessary while doing data analysis. However, the manual method of calculating those is not only time-consuming but also tiresome. In this article, Iâ€™ll demonstrate how to calculate the average and standard deviation using Excel formulas with proper explanation.

**Table of Contents**hide

## Basics of Average and Standard Deviation

### What is Average

An average is mainly a number that represents the typical value of a list of numbers. Often we can call it the arithmetic mean. However, we are using the following formula for measuring the average.

**Average = Sum of All Values/Number of Values**

### What is Standard Deviation

Basically, the standard deviation is a quantity representing the amount of dispersion. That means it refers to the deviation of a value from the mean of all values. The equation for measuring standard deviation is-

**âˆš((âˆ‘(Xi-Âµ)Â²/n))**

Here,**X _{i }**is the i-th value of the entire population,

**Î¼**Â is the mean value,

**n**Â is the number of values of the entire population.

Letâ€™s be introduced to todayâ€™s dataset where the names of students with their *ID* & scores are given. Now, we need to calculate the average and standard deviation for the below dataset.

## 1. Calculating Average in Excel

In the beginning, Iâ€™ll discuss the calculation process of average in different ways. For example, youâ€™ll see the methods measuring the average manually and using a function. Then youâ€™ll explore the average calculation for multiple ranges on the basis of criteria.

**1.1. Computing Average Manually**

While grasping the equation of calculating the average, you might notice that we need to apply the **SUM **and **COUNT** functions to compute the average manually. So, the formula will be as follows.

`=SUM(D5:D12)/COUNT(D5:D12)`

Here, the **SUM **function returns the total scores and the **COUNT **function counts the number of scores. Finally, total scores are divided by the number of scores.

**1.2. Calculating Average Using Function**

We may use **the AVERAGE** **function** instead of using the manual method. The formula will be:

`=AVERAGE(D5:D12)`

Here, **D5:D12 **is the cell range of scores.

Though the output using the manual and function is the same, the next method is more time-saving and fast.

**Read More:** How to Calculate Sum & Average with Excel Formula

**1.3. Measuring Average If Multiple Ranges**

If you look closely at the following screenshot, you may see that we need to compute average scores for *Physics, **Chemistry,* and *Psychology*. That is to say, we are going to measure the average for multiple ranges.

To do this, follow the steps below.

âž¯ Firstly, select the entire dataset and go to the **Formulas **tab.

âž¯ Next, choose the **Create from Selection **option from the **Defined Names** ribbon. We are going to use the **Name Manager** to save our dataset and recall it quickly for further use.

âž¯ Immediately, youâ€™ll get the following dialog box and check the box before the **Top row**.

Now, you have to use the following formula for *Chemistry* (**H7** cell).

`=AVERAGE(INDIRECT(H7))`

Here, **the INDIRECT function** finds all the scores in *Chemistry* as directed by cell **H7**. Later, the **AVERAGE **function measures the average scores in the subject.

âž¯ However, if you use the **Fill Handle** to copy the formula for the cells below, youâ€™ll get the average scores for other subjects sequentially.

**1.4. Determining Average with Criteria**

Interestingly, you can measure the average based on criteria. Letâ€™s say, a student obtains 0 and you need to ignore the 0. In other words, you want to calculate the average of scores greater than 0. In such a situation, **the AVERAGEIF** **function** might be helpful for you.

`=AVERAGEIF(D5:D12,">0")`

Here, **D5:D12 **is the range of scores and **>0 **is for finding the average for excluding 0.

Likewise, if you wish to measure the average of scores greater than 80. The formula will be:

`=AVERAGEIF(D5:D12,">80")`

Here, **>80 **is for computing the average including the scores above 80.

## 2. Calculating Standard Deviation in Excel

Now, Iâ€™ll discuss the process of computing standard deviation in many ways e.g. manually, using the built-in function, and lastly measuring standard deviation ignoring text values.

**2.1. Determining the Standard Deviation ManuallyÂ **

In the manual method, we need to apply the combination of **SQRT**, **SUM**, and **COUNT **functions to determine the standard deviation based on the equation.

So, the formula for finding the standard deviation for the entire population (when considering all values in a given dataset) will be like the following.

`=SQRT(SUM(D5:D12)/COUNT(D5:D12))`

Here, the **SQRT **function returns the square root of the output found by dividing the total scores by the number of scores.

If we consider sample data from the entire population, the formula will be:

`=SQRT(SUM(D5:D12)/COUNT(D5:D12)-1)`

In this case, we need to subtract 1 from the number of the entire population.

**2.2. Computing Standard Deviation Applying Function**

To obtain the value of standard deviation quickly, we may use **the STDEV function** in Excel.

`=STDEV(D5:D12)`

Here, **D5:D12 **is the range of scores.

**Read More:** How to Calculate Average Deviation in Excel Formula

**2.3. Measuring Standard Deviation for Sample Data (STDEV.S function)**

If you want to calculate the standard deviation for a sample of data from the entire population, using the **STDEV.S** function will be a better option for you.

`=STDEV.S(D5:D12)`

**Note**

*Microsoft suggests using the*

**STDEV.S**for a sample of data instead of using the**STDEV**though the two functions return the same output.**2.4. Calculating Standard Deviation for Entire Population (STDEV.P function)**

Again, if your requirement is to calculate the standard deviation for the entire population, you can use the **STDEV.P** function. So, the formula for the dataset will be:

`=STDEV.P(D5:D12)`

**2.5. Computing Standard Deviation Ignoring Text (STDEVPA Function)**

Sometimes, we ought to ignore the text and other logical values while measuring the standard deviation. Assuming that we find *No data*Â (Cell **D12**Â in the dataset) for a student. Right away, we need to use the **STDEVPA**Â function to ignore the text value. Just use the following formula.

`=STDEVPA(D5:D12)`

Here, the above picture reveals that the standard deviation including the text value (while using the **STDEV.P** function) and excluding the text value (in the case of the **STDEVPA **function) are not the same.

## Common Errors to Calculate Average and Standard Deviation in Excel

Name of Errors | When Occurs |
---|---|

#N/A! |
The AVERAGE function returns if the dataset contains any error. |

#VALUE! |
The STDEV function returns if there exist any text values in the dataset. |

#DIV/0! |
The STDEV function returns if there exists only one numeric value in the dataset. |

**Download Practice Workbook**

## Conclusion

In short, apply the above-discussed methods to calculate the average and standard deviation in Excel. However, if you have any queries or suggestions, please share them in the comments section below.

## Related Articles

- How to Calculate Average Excluding Outliers in Excel
- How to Calculate Average of Text in Excel
- How to Average Negative and Positive Numbers in Excel
- How to Do Subtotal Average in Excel
- How to Calculate Average of Top 5 Values in Excel
- How to Calculate Average from Different Sheets in Excel
- How to Average Filtered Data in Excel