## Basics of Average and Standard Deviation

### What is Average

The average is the arithmetic mean.

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

### What is Standard Deviation

Standard deviation is a quantity representing the amount of dispersion. 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.

This is the sample dataset. To calculate the average and standard deviation:

## 1. Calculating the Average in Excel

**1.1. Computing the Average Manually**

Apply the **SUM **and **COUNT** functions to compute the average manually. The formula is:

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

The **SUM **function returns the total scores and the **COUNT **function counts the number of scores. Total scores are divided by the number of scores.

**1.2. Calculating the Average Using a Function**

Use **the AVERAGE** **function**. The formula is:

`=AVERAGE(D5:D12)`

**D5:D12 **is the cell range.

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

**1.3. Calculating the Average for Multiple Ranges**

Use the average calculation for multiple ranges.

To compute average scores for *Physics, **Chemistry,* and *Psychology:*

- Select the entire dataset and go to the
**Formulas**tab. - Choose
**Create from Selection**in**Defined Names**. - Use the
**Name Manager**to save the dataset.

- In the dialog box, check
**Top row**.

Use the following formula for *Chemistry*: **H7**.

`=AVERAGE(INDIRECT(H7))`

**The INDIRECT function** finds all scores in *Chemistry*, the **AVERAGE **function calculates the average scores.

- Use the
**Fill Handle**to copy the formula.

**1.4. Determining the Average with Criteria**

A student obtains 0 and you need to ignore the 0. To calculate the average of scores greater than 0, use **the AVERAGEIF** **function**.

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

**D5:D12 **is the range and **>0 **finds the average excluding 0.

To measure the average of scores greater than 80, the formula is:

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

**>80 **calculates the average including scores above 80.

## 2. Calculating the Standard Deviation in Excel

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

Combine the **SQRT**, **SUM**, and **COUNT **functions.

Use this formula:

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

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

If we consider the sample data, the formula will be:

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

Subtract 1 from the number of the entire population.

**2.2. Computing the Standard Deviation by Applying a Function**

Use **the STDEV function**.

`=STDEV(D5:D12)`

**D5:D12 **is the range.

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

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

Use the **STDEV.S** function.

`=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 the Standard Deviation for the Entire Population (STDEV.P function)**

Use the **STDEV.P** function. The formula is:

`=STDEV.P(D5:D12)`

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

Use the **STDEVPA** function to ignore the text value. Use the following formula.

`=STDEVPA(D5:D12)`

## Common Errors while Calculating Average and Standard Deviation in Excel

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

#N/A! |
Â the dataset contains an error. |

#VALUE! |
there are text values in the dataset. |

#DIV/0! |
Â there is only one numeric value in the dataset. |

**Download Practice Workbook**

## 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 Average Filtered Data in Excel

**<< Go Back to Calculate Average in Excel | How to Calculate in ExcelÂ | Learn Excel**