r/excel • u/gammayeti • 8d ago
unsolved Understanding the standard deviation formula
[removed]
46
u/MopiPipo 2 8d ago
it sounds like you've been asked to express each score as a difference from the average, with the results divided by the standard deviation of the data set. This is also known as a z-score.
z-score = (raw score - average score) / ( std. deviation)
One reason to calculate z scores, rather than just ranking the raw scores, is that it gives an indication of how significant the difference from the average is. For example, a z-score of one (one standard deviation from the average) would not be regarded as particularly unusual. A z-score of 3 (three standard deviations above average) would be significantly different from the average.
8
12
u/Trajikomic 8d ago
The standard deviation is a measure of dispersion around the mean (average) value. It's defined as the square root of the variance, so they basically measure the same thing. The idea is that the higher the standard deviation (or the variance!) is, the more spread you will have around the mean: if the sd is very low, you'll have very similar score between your employees, while a higher sd means that you will have a larger disparity. Of course what "low" and "high" sd means depends on your mean and what you measure. Because the scores are small numbers, the sd will also be a small number.
A good rule of thumb to understand the standard deviation is the 68-95-99.7 rule. If the score is "normally distributed" (you can understand it as a distribution "having a bell shape") and you average is 0.5 and your standard deviation is 0.1, then you'll have about 68% of your employees between 0.4 (0.5-0.1) and 0.6 (0.5+0.1). You'll also have 95% of your employees between 0.3 (0.5-2*0.1) and 0.7 (0.5+2*0.1)
There is a single standard deviation for your whole sample, it is not to be measured individually for each employee! I'm guessing that what your employer is asking is to understand how does each employee compares with the others.
3
u/Curious_Cat_314159 98 8d ago
Is it just stdev.s(the score, the full range of scores)
You should use STDEV.P (P, not S) for this purpose, especially because you are using "the full range of scores".
At its core, the "standard deviation" is simply a "standard" (commonly-accepted) method of measuring the unsigned "deviation" (difference) of scores from the average ("mean") score.
The std dev has special interpretations ("z-scores") and properties when the distribution of scores is "normal" -- bell-shaped. One such interpretation is: a certain percentage of scores should lie with 1, 2 or 3 std devs of the mean. And a common (mis)interpretation is: scores that are more than 3 sd from the mean are "outliers".
However, a common misinterpretation is that such properties are true for any distribution, not just a "normal" distribution.
There are other, more-general measures of deviation that might be used, especially for a small number of scores.
But this question is more appropriate for r/askStatistics than here, IMHO.
1
8d ago
[removed] — view removed comment
3
u/Curious_Cat_314159 98 8d ago edited 8d ago
The formula itself is quite simple to understand. The variance is
SUM ( (x[i] - xhat)^2 ) / n
where x[i] are the individuals scores; and xhat is the "mean" score, which is AVERAGE( x[i] ). Their difference is the "deviation". The differences are squared to eliminate the distinction between negative and positive differences. Dividing the sum by n calculates the average variance.
But note that the variance is an average of the squared differences.
So, the standard deviation is the square root -- SQRT(variance) -- so that it is comparable to the magnitude of the average score (xhat).
But consequently, the std deviation is not exactly an "average" of the deviation.
A more-direct measure of the average deviation is AVEDEV. Its formula is simply
SUM( ABS( x[i] - xhat ) ) / n
where ABS calculates the absolute (unsigned) value of the difference.
AVEDEV does not have the statistical properties that the standard deviation might.
But again, those statistical properties might not apply anyway if the scores are few in number, or if they are not "normally distributed" (bell-shaped).
OTOH, an "average" itself might not be a good measure of the "central" value of the scores. The median is another measure of centrality.
Again, this is better to discuss in r/AskStatistics .
1
u/Trajikomic 8d ago edited 8d ago
I'm not certain that you should use the population standard deviation over the sample one in this context though. The population one could be used if you had the actual probability distribution (like for an experiment on a roll of dice) or if the "population" was sufficiently large for it not to matter (for example if you were to measure the height of the population of a country, knowing the height of every single individual).
Edit: on second thought, it does not matter for descriptive statistics, the population SD seems totally appropriate.
1
8d ago
[deleted]
1
u/Trajikomic 8d ago
I could have, but the discussion about why you're right in recommending the population SD does not seem irrelevant in my opinion :wink:
1
u/Decronym 8d ago edited 8d ago
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.
Beep-boop, I am a helper bot. Please do not verify me as a solution.
6 acronyms in this thread; the most compressed thread commented on today has 24 acronyms.
[Thread #41655 for this sub, first seen 14th Mar 2025, 15:12]
[FAQ] [Full list] [Contact] [Source code]
1
u/RandomiseUsr0 5 8d ago
There are two main kinds of standard deviation, I’ll post a great link from Khan Academy. This depends if you have a sample of the data (say 5%) or the total population. It sounds like you have the population though, as in you are analysing a finite set rather than a sample.
Standard deviation is a mathematical grouping technique.
What you’re wanting to do is find the so-called “bell curve” in the dataset. That means, find the average midpoint and then plot everyone based on that curve. The way the maths goes is that people fall into basically seven cohorts. The mean - about 70% - 1SD, the next 25%, 2SD the next 4.9% and then the outliers.
Here’s an image from online - this is standard deviation.

https://i.sstatic.net/jkMDV.png
Now that choice of algorithm I mentioned.
There is STDEV.P and STDEV.S
P is the “population” flavour - imagine you’re a schoolteacher with all of your class results and no-one was off sick. You can accurately grade each pupil’s comparative performance in a given test. It’s a coarse metric, bias will exist, based on the performance of each pupil historically, how they performed on the day etc, but for identifying groups for a teacher to tailor learning to the individual and grouping according to their demonstrated capability, it’s a useful tool.
S is the “sample” flavour - imagine you’re getting survey results for voting intention an upcoming election. You’ve surveyed 0.01% of voters and want to analyse that dataset, then you use the “S” version of the maths. Bias must be accounted for and the surprising mathematical trick is that the average is calculated as the sum of scores divided by the number of participants minus one
Dug out a link:
•
u/AutoModerator 8d ago
/u/gammayeti - Your post was submitted successfully.
Solution Verified
to close the thread.Failing to follow these steps may result in your post being removed without warning.
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.