•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Standard Deviation - Manual vs. Excel Formula
Our take
In this discussion, we delve into the nuances of calculating standard deviation in Excel, comparing manual methods with the built-in =STDEV.S function. The left side of the image shows a standard deviation of 0.12 using Excel's formula, while the manual calculation appears to yield a different result. The manual approach follows a clear six-step process, yet discrepancies arise despite both methods employing the "n-1" technique. Let's explore what might be causing this inconsistency and clarify how to ensure accurate results in your calculations.
Hi all,
Having some issues with understanding how Excel is calculating the data for SD. Tried it using both manually and using the =STDEV.S function.
Left hand side: S.D is 0.12 (=STDEV.S)
Right hand side: using this method:
- Step 1: Find the mean.
- Step 2: Subtract the mean from each score.
- Step 3: Square each deviation.
- Step 4: Add the squared deviations.
- Step 5: Divide the sum by one less than the number of data points.
- Step 6: Take the square root of the result from Step 5.
Now, as you can see in yellow, the values don't match. I verified that the =STDEV.S is using the "n-1" method, which matches my manual method.
Any idea what I'm missing here?
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Getting unexpected results when generating descriptive statistics for a column of Z scoresHello, I am new to excel, so hopefully this is an obvious question with an easy fix. But I am getting some really weird results when trying to work with the z-scores that I generate for a data set. The formula I am using is =standardize(x,mean,stdev) where X is the cell number I am referring to. Then once executing that formula, I copy it and paste it down the column to get the Z scores for all 170 data points. When eyeballing those results, it looks right, but I’m getting unexpected results when I try to average them and get the new standard deviation. The formula I use for average is =average(b2:b172) and I am getting -4 point something. For standard deviation, I am using =stdev.p(b2:b172). I have also tried getting the sample standard deviation, stdev.s, and I am getting unexpected results. I expect my standard deviation to be around two. I also have the data analysis pack enabled and tried generating descriptive statistics on the Z score column with the same results as with the formulas. I have tried calculating the Z score using the value for stdev.s and stdev.p, as well as the standard error that is generated by the descriptive statistics. Another thing to note is that there were two cells with a Null in them so I removed them from the column and adjusted my formulas accordingly. I am wondering if there is an error I’m making with one of my formulas or in working with my data, or maybe if I don’t fully understand what is needed to get Z scores. I am taking an online statistics class right now and trying to teach myself to use Excel in place of the software that is used for the class because I use a screen reader on my computer And the course software is not accessible with that so I am just trying to figure out how to do all of this. Any ideas of other things I could try would be helpful. submitted by /u/qwirkycactus [link] [comments]
- StDev formulas all giving #DIV/0! despite no "0" or error cellsIn column H I have an average speed calculated using columns E and F, whilst excluding outlier values. I am using the formula below: =IF(AND(160>F3/(E3/60),F3/(E3/60)>0),F3/(E3/60),"") On another sheet in cell "C5" I am trying to find the SD of column H using the formula: =STDEV.P('EV Data'!H3:H100000) The #Div/0! error keeps coming up despite there being no zeros due to the formula in column H. I have also checked using the filter function and there are no ERROR, DIV/0!, or N/A cells in column H. Any help would be greatly appreciated. TIA submitted by /u/Turbine_2022 [link] [comments]