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)
[gravityform id=”2″ title=”true” description=”false”]