Solved

Standard Deviation in Excel

Posted on 2011-09-21
7
330 Views
Last Modified: 2012-08-13
I don't understand why the value of cell B9 is not equal the value in C8 in the attached spreadsheet. I think they both should be 3.741657.
standardDeviation.xlsx
0
Comment
Question by:allelopath
  • 3
  • 3
7 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 36574974
they are two different functions

B9 = stddevp(B2:B7)  deviation over entire population N
C8=B10 = stddev(B2:B7) deviation over "almost" entire population N-1

0
 
LVL 1

Author Comment

by:allelopath
ID: 36575005
Yes, but cell C8 is over the entire population, hence I would expect it to be the same as B9, which is also over the entire population.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36575023
VAR is also non-biased   i.e.  N-1  (not the entire population)


stddev = sqrt(var)  because both are non biased

stddevp <> sqrt(var)  because stddevp is biased
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 36575046
if you want biased variance over the entire population  use VARP  instead of VAR

stddevp = sqrt(varp)
0
 
LVL 1

Author Comment

by:allelopath
ID: 36575053
Oh. Is there VAR for N? I don't see a VARP function.
0
 
LVL 1

Author Comment

by:allelopath
ID: 36575055
oh yes I do ...
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36581075
allelopath,

Your entire population has 7 members?  Really?

Sarcasm aside, you will nearly always want the sample stdev and sample variance; in actual practice, you will almost never have statistics for the entire population, and once your sample gets to a sufficient size, the difference between the sample and population versions of stdev and variance rapidly approaches zero.

Patrick
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question