Md. Abdul Kader
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-
√((∑(Xi-µ)²/n))
Here,
Xi 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
- How to Calculate Standard Deviation of a Frequency Distribution in Excel
- 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 Calculate Average Percentage of Marks in Excel (Top 4 Methods)
- Calculate Moving Average in Excel (4 Examples)
- Calculate Average of Text in Excel (2 Ways)
- How to Calculate Average Rating in Excel (7 Methods)
