Keyboard Ninja

Calculating Variance and Standard Deviation in Excel

By MoeLucie @moelucie

So, you need to calculate variance and standard deviation in Excel? I am assuming that you are in stats right now. I would not imagine you would want to do these calculations to fulfill something on your bucket list.

So Let’s Get It Over With!

There are a few ways you can do these calculations.

The first is

by putting your data set into a column

Once there enter the desired cell where you will put the answer

Now, start with pressing the = sign

You will hear your screen reader say enter the formula and press enter

Woo! Getting to the Nitty Gritty Now!

To calculate variance

Type VAR (Victor, Alpha, Romeo)

Then determine if your data is from a sample or population

Hmmm….

If it is from a sample (not the whole population)

Enter period s (sierra) after VAR

Without any spacing

If you have a population, enter period p (papa)

Hang In There We Are Almost Done!

Now that we have the beginning sorted out lets determine whose getting calculated

Open your prentices and enter the name of the first cell name you want to calculate

Then put a colon in between (sounds kind of weird)

Now enter the second cell address at the end of the data

Close those prentices and press enter!!!!

Example: VAR.s(A1:A5)

Do not put any spaces or Excel gets very mad at you and tells you to stop!

Still With Me?

There is another way!

The beginning is the same but inside the prentices is different

Instead of cell addresses you enter the data separated by commas

For Example: VAR.s(1,2,3,4,5)

Now Are You Hyped Up Enough for Standard Deviation?

Lucky for us the concept is very similar!

Start with the = sign again

Now type STDEV

(Sierra, Tango, Delta, Echo, Victor)

Enter a period

Do not forget about papa and sierra!

Now open the prentices and enter either cell addresses or data values

Example: STDEV.s(A1:A5)

Or, STDEV.s(1,2,3,4,5)

Okay We are Done

Did We Do It Right?

Remember that Variance is a squared value, so it cannot be negative! If your answer is negative it is wrong!

Your standard deviation value should be smaller than your variance

Variance is the squared value of standard deviation

Standard deviation is the square root of variance

Try it out square your standard deviation, does it match your variance?

For example if your variance is 4, your standard deviation is 2

They will never be negative!

Checking Our Work:

In our example VAR.s(1,2,3,4,5) the answer is 2.5

Standard Deviation is; 1.58 (rounded to 2 decimal places)