Var & VarP, StdDev & StdDevP

Hello All,
Sorry to be so cheaky and ask multiple questions but I thought I may as well get the most of you clever clogs as I can.

I am having a bit of a blonde day and I don't get something well two things

1) Why does StdDev from field settings within the pivot table menu only do standard deviation like this.

a) AVERAGEs all the values
b) Then its the new average minus each individual value
c) Then all the new average values minus each individual values squared
d) The its all added up and divided by all the elements - 1
e) Then square rooted

My question is why in section d is it -1 for StdDev and for StdDevP it isn't. I always go taught Standard Deviation should be calculated as it is in StdDev.

2) My second question is purely what is Var and VarP about? Is it the variance and if so how or what am I suposed to do with that?

1. Excel didn't invent the functions; it just implements them. There's a decent explanation of the difference between computing the deviation of an entire population versus a sample at
http://en.wikipedia.org/wiki/Standard_deviation.

2. Variance is the square of the deviation. Why are you computing either if you don't know what to do with them?

Then I expect you want a different forum -- maybe the math forum at Drexel (mathforum.org) or talkstats.com.

This forum is all about how, not why.

Originally Posted by shg

1. Excel didn't invent the functions; it just implements them. There's a decent explanation of the difference between computing the deviation of an entire population versus a sample at
http://en.wikipedia.org/wiki/Standard_deviation.

ok, I'm a bit late to the party here, but found this thread via a search engine when I was looking up a problem I had with the stdev in Excel (actually, OpenOffice, but I guess Excel handles it the same). And I did read the Wiki article you referenced above and am quite confident that I understood it, because whenever I use the examples given in the Wiki to caclulate manually, I get the correct values, but Excel has them all the other way round, as DaveyB tried to point out.
stdev() in Excel uses N-1 to calculate standard deviation while stdevp() uses N. According to the definition on the Wiki (and in statistics in general, I might add), N as a quotient however, is part of the definition of the standard deviation, whereas N-1 is part of the definition of sample standard deviation. So it really is not a problem of understanding, but it's a problem that for some reason stdev() gives the values of sample standard deviation, and stdevp() the values for standard deviation.
I had always assumed, that the 'p' in stdevp stands for 'population', since that is where sample standard deviation is used mostly, but looking at the values, something is definitely wrong here. Either my inference that stdevp() is the sample standard deviation is wrong, or the values of the functions are wrong.

ok, make that 'divisor' where it says 'quotient', don't want to be bashed on details;-)

Originally Posted by shg

Is your point that Microsoft should rename the functions? I think it's a little late for that.

Nope, that's not the point. But they messed it up, if I am not mistaken. And it cost me quite a bit of time today, since I was checking the output of a program of mine against a spreadsheet and thought I had it wrong. But obviously everybody does it, OpenOffice has it also the other way round.

Originally Posted by shg

I think it's prudent to understand what worksheet functions actually do, rather than rely on their names to inform. And Help is very clear for the statistical functions that are solved in closed form.

Ummm, so that basically means that, if Microsoft chooses to call red green and green red, I should just refer to the 'help' section?

On a side note: I can feel your pain here, brother. I am a programmer and I also loathe to change a line of code in a running program, just because somebody cannot get along with my wording.
But your argument that changing that would be a little late is only understandable from the technical point of view, that, to maintain compatibility, this mistake has to be perpetuated. However, the naming of those functions has been long prior to Excel so, to stay with the red/green analogy, the take-home message is: MS has f... up, so just deal with it and update the Webster!

ok, so amending my prior statements, stdevp's naming would be correct, assuming that it stands for population standard deviation, so the name tags have not been exchanged between the two incarnations. However, everywhere except in excel, population standard deviation is THE standard deviation (or standard standard deviation, if you may;-), so having sample standard deviation occupying the spot for the default is still wrong. Well, I guess nobody cares and so I have to get used to that and move on...

does Vipassana-Meditation also count? Must be, because currently I have problems practising since I ruptured a ligament in my ankle a week ago and cannot assume the positions (sitting, kneeing) that I usually occupy, so I might by a bit out of kilter. Maybe after getting better with my ankle, I can rest the case of the screwed up stdev-naming:-D

ok, and my orthography is also off 'I might be' instead of 'by'...

I appreciate this is long after the start date, but I have been wrestling with same question of why N or N-1. If this is indeed the original question raised in this thread then my understanding is now that it relates to the difference between 'population' and 'sample'. More specifically 'sample variance' uses N-1 and 'standard variance' (population) uses N (as mentioned about three quarters the way down http://en.wikipedia.org/wiki/Standard_deviation). This then left me wondering why the different treatment and I found an explanation at http://www.beabu.com/difference-popu...ard-deviation/ under the paragraph titled "The difference in calculation: population vs. sample variance" which explains a statistical compensation which is required. I then revisited Excel Help for VAR which very specifically refers to 'sample', hence the N-1. Hope this helps. Andy.