We frequently use Excel to calculate different kinds of averages (mean, median, mode etc.) for different sets of numbers. The size of the dataset varies and can often be super huge.
The contemporary mean function finds the average of a data set by adding up all the values of the dataset and dividing them by the number of values. However, mean is often only calculated for a sample of the population and not for the entire population.
This can lead to errors. And to counter these errors caused by computing the mean for only a sample from the population, we estimate the standard error.
Table of Contents
What is a Standard Error?
The standard error of mean is used to estimate the difference between the mean of a sample and the true mean of the entire population. Must’ve made only a little sense. See below.
An Example:
Suppose you take up the project to estimate the average age of the people of a town that has a population of 1 million. It might not be feasible for you to collect data for every single person from this population. So, you may resort to sampling by picking up a sample size of 5000 people out of the total population.
Noting down the age of each of these 5000 people, you can find the average age for this sample. This can then be projected to the entire population of 1 million. Let’s say, this number works out to be 32 years.
The average age of 32 years is an approximation of the average age for the people living in that town. However, as this average age is only computed based on a sample, there are high chances that it won’t be a true representative of the entire 1 million population.
Had you averaged out the age of all the 1 million people living in that town, the results might have been slightly different – say 32.7 years.
This error of 0.7 (32.7 less 32) is a standard error.
Note: A standard error is negatively correlated to the sample size. The larger the sample size, the smaller the standard error.
How To Calculate Standard Error?
A standard error is the approximate standard deviation for a given sample. Excel doesn’t offer an in-built function to calculate the standard error for a given dataset in one go. However, by combining a few functions, the standard error for a given dataset can be conveniently found in Excel.
There are two ways how this might be calculated in Excel. Let’s look into them both.
1. A mathematical formula
The formula below must take you back to your school times.
Not only relevant theoretically, but you can also apply this formula to Excel by breaking down the calculation into a few simple steps.
In the image below, we have a data set that has 10 numbers.
To find the standard error for the mean of these 10 numbers using the above formula, stick to the steps below.
Step 1:
Calculate the average for the sample data using the AVERAGE function of Excel as shown below.
Step 2:
Calculate the difference for each value from the dataset against the average for the data set.
Pro Tip: If you plan on using the ‘Drag and drop’ function of Excel to calculate the differences in a snap, do not forget to turn the cell reference containing the average into an absolute reference. To do so, simply click on the cell reference in the formula bar (B14 in this case) and press F4.
Step 3:
Square the differences (Column B).
Pro Tip: If the exponential power sign (^) is nowhere to be found on your keyboard, do not worry. You can set up the formula as (B2 * B2) or (B3 * B3) etc.
Step 4:
Sum up the square values.
Step 5:
To take the formula ahead, you need to know the number of data points, which in the given example is 10. In the instance under question, the count was an easy one and could have been performed manually.
However, this might not always be possible, particularly with voluminous data sets. To find the number of data points, you can employ the COUNT function as follows.
= COUNT ( A2: A11 )
Step 6:
While we have all our figures ready, it’s time we put them into the formula to find the Standard Error as shown below.
= SQRT (B15 / (B16 * (B16-1) ) )
And there you have the standard error of 0.5617, all calculated in Excel.
2. Combining the STDEV.S and SQRT function
Another method how you may find standard errors in Excel is by using the standard deviation and the square root functions of Excel.
A short way to compose the mathematical formula explained above is as follows.
In the above formula, the sign ‘σ’ represents ‘standard deviation’.
Simply put, standard error can be calculated by dividing the standard deviation for a given dataset by the square root of the number of data points in a dataset.
The example below should demonstrate how this formula can be applied in Excel.
Step 1:
Continuing the same data set as above, the first step is to calculate the standard deviation for the dataset. Excel offers an in-built function to calculate standard deviation and all you need to do is set up the formula.
= STDEV.S (A2:A11)
Step 2:
Calculate the number of data points in your dataset, you may want to do this manually or by using the COUNT function.
= COUNT ( A2: A11 )
Step 3:
Put together the formula for standard error by using the SQRT function as demonstrated below.
= B14 / SQRT (B15)
Excel calculates the standard error of 0.5617.
Must note how the answer remains constant between both the methods of computing standard error in Excel.
How To Put Standard Error Bars In Excel?
For a better visual representation, you can turn your dataset into a graph. For instance, in the image below, three bars represent three different averages.
After a bar chart has been constructed, you may add standard error bars to it to know the possible deviation in actual results. To add standard error bars to your bar chart, follow the steps below.
Step 1:
Click anywhere on the chart to activate the chart formatting options. Tap on the ‘plus’ sign on the right and choose ‘Error bars’ as shown below.
Step 2:
This will add error bars to the top of each bar, as shown above. Click on these small bars to open editing options for the same.
From these options, select ‘Standard Error’ and other options to shape the error bars as you may like.
Adding error bars to Excel is only that easy, and this helps the viewers know the possible extent of deviation from the visualized figures.
Conclusion:
Mean and other averages are super common and massively used in all fields of life – be it a mathematics assignment or a statistical forecasting model. With such extensive use of sample averaging methods, it is essential to know the possible loopholes with sample averaging.
Not only that, but you must also know and master measures to counter the effects of such loopholes. Calculating and adjusting the standard error is one of them, and with Excel, this calculation becomes super easy.
Practice with the examples stipulated above to master standard error calculation.