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.
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.
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-
Here,
Xi 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)
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
