**Calculating average** and standard deviation are often necessary while doing data analysis. But 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.

## Download Practice Workbook

## Basics of Average and Standard Deviation

### What is Average

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 main equation for measuring standard deviation is-

`√((∑(X`

_{i}`-µ)²/n))`

Here,

**X _{i }**is the ith value of the entire population

**μ** is the mean value

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

## Methods to Calculate Average and Standard Deviation in Excel

Let’s be introduced with today’s dataset where the **Name **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 utilize 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 latter method is more time-saving and fast.

**1.3. Calculating 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 the **H7 **cell. Later, the **AVERAGE **function measures the average scores in the subject.

➯ However, if you use the **Fill Handle Tool** to copy the formula for the cells below, you’ll get the average scores for other subjects sequentially.

**1. 4. Calculating 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 cell 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**.

**Read More: ****How to Calculate Average of Multiple Columns in Excel (6 Methods)**

**Similar Readings**

**Get Average Time in Excel (3 Examples)****How to Calculate 7 Day Moving Average in Excel (4 Ways)****Exclude a Cell in Excel AVERAGE Formula (4 Methods)****How to Find Outliers with Standard Deviation in Excel (with Quick Steps)**

### 2. Calculating Standard Deviation in Excel

Now, I’ll discuss the process of computing standard deviation in many ways e.g. manual, 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 **SQRT**, **SUM, **and **COUNT **functions combined to determine the standard deviation based on the equation.

So, the formula while 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.

But the formula, if we consider a sample data from the entire population, 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.

**Read More: Plot Normal Distribution in Excel with Mean and Standard Deviation**

**2. 2. Computing Standard Deviation Utilizing Function**

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

`=STDEV(D5:D12)`

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

**Read More:** **How to Calculate/Find Mean and Standard Deviation in Excel**

**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** 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)`

**Read More: ****Generate Random Number with Mean and Standard Deviation in Excel**

**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**’ (**D12** cell in the dataset) for a student. Right away, we need to use the **STDEVPA** function for ignoring 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.

**Read More: How to Calculate Mean Variance and Standard Deviation in Excel**

## 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. |

## 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 VLOOKUP AVERAGE in Excel (6 Quick Ways)****Calculate Average, Minimum And Maximum in Excel (4 Easy Ways)****How to Generate Moving Average in Excel Chart (4 Methods)****Calculate Moving Average in Excel (4 Examples)****How to Calculate Standard Deviation of a Frequency Distribution in Excel****How to Calculate Exponential Moving Average in Excel****Calculate Average of Text in Excel (2 Ways)****How to Calculate Average Percentage of Marks in Excel (Top 4 Methods)****How to Calculate Average Rating in Excel (7 Methods)**