How to calculate average error in excel

In this guide, I will show you how to calculate the mean (average), standard deviation (SD) and standard error of the mean (SEM) by using Microsoft Excel.

Calculating the mean and standard deviation in Excel is pretty easy. These have built-in functions already available.

Calculating the standard error in Excel, however, is a bit trickier. There is no formula within Excel to use for this, so I will show you how to calculate this manually.

How to calculate the mean value in Excel

The mean, or average, is the sum of the values, divided by the number of values in the group.

To calculate the mean, follow the steps below.

1. Click on an empty cell where you want the mean value to be.

2. Enter the following formula.

=AVERAGE(number1:number2)

Then change the following:

  • Number1 – the cell that is at the start of the list of values
  • Number2 – the cell that is at the end of the list of values

You can simply click and drag on the values within Excel instead of typing the cell names.

How to calculate average error in excel

3. Then press the ‘enter’ button to calculate the mean value.

How to calculate the standard deviation in Excel

The standard deviation (SD) is a value to indicate the spread of values around the mean value.

To calculate the SD in Excel, follow the steps below.

1 Click on an empty cell where you want the SD to be.

2. Enter the following formula

=STDEV(number1:number2)

Then, as with the mean calculation, change the following:

  • Number1 – the cell that is at the start of the list of values
  • Number2 – the cell that is at the end of the list of values
How to calculate average error in excel

3. Then press the ‘enter’ button to calculate the SD.

How to calculate the standard error in Excel

The standard error (SE), or standard error of the mean (SEM), is a value that corresponds to the standard deviation of a sampling distribution, relative to the mean value.

The formula for the SE is the SD divided by the square root of the number of values n the data set (n).

To calculate the SE in Excel, follow the steps below.

1. Click on an empty cell where you want the SE to be.

2. Enter the following into the cell:

=STDEV(number1:number2)/SQRT(COUNT(number1:number2))

Change the following throughout:

  • Number1 – the cell that is at the start of the list of values
  • Number2 – the cell that is at the end of the list of values

It is worth noting that instead of using the COUNT function, you can simply type in the number of values in the data set. In this example, this would be 9.

How to calculate average error in excel

3. Then press the ‘enter’ button to calculate the SE.

Conclusion

In this tutorial, I have described how to calculate the mean, SD and SE by using Microsoft Excel.

Microsoft Excel version used: Office 365 ProPlus

How is average error calculated?

Step 1: Calculate the mean (Total of all samples divided by the number of samples). Step 2: Calculate each measurement's deviation from the mean (Mean minus the individual measurement). Step 3: Square each deviation from mean. Squared negatives become positive.