How to Calculate Average and Standard Deviation in Excel

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.

How to Calculate Average and Standard Deviation in Excel 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.

Computing Average Manually


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.

How to Calculate Average and Standard Deviation in Excel Calculating Average using Function


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.

Calculating Average If 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.

Calculating Average If Multiple Ranges

➯ Immediately, you’ll get the following dialog box and check the box before the Top row.

Calculating Average If Multiple Ranges

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.

Calculating Average If Multiple Ranges


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.

How to Calculate Average and Standard Deviation in Excel Calculating Average with Criteria

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


Similar Readings


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.

How to Calculate Average and Standard Deviation in Excel Determining the Standard Deviation Manually

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.

Computing Standard Deviation Utilizing Function

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)

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

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)

How to Calculate Average and Standard Deviation in Excel (STDEV.P function)

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)

How to Calculate Average and Standard Deviation in Excel STDEVPA Function

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

Md. Abdul Kader

Md. Abdul Kader

Hi! I am Abdul Kader and presently working as ‘Excel & VBA Content Developer’ at Exceldemy. I publish my articles related to Microsoft Excel here. In 2019, I completed my graduation in Urban and Regional Planning from Chittagong University of Engineering and Technology. Having extreme eagerness to learn, I want to develop my skills and capabilities higher.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo