I want to calculate how balanced a series of three numbers are. Calculating the standard deviation only works when the basis is the same. For example:
A B C Average Std Dev.P
1 6 5 4 5 .8
Equal-weighted standard deviation has an understood meaning, but asset-weighting, to my knowledge, doesn't. It's more complex to derive and provides you with what benefit? And, why would using the beginning of the year market value for an annual measure improve upon the tried-and-true equal weighted standard deviation?
2 8 4 3 5 2.1 obviously, row 2 is not as close to balanced as row 1 and has a higher standard deviation
3 30 25 20 25 4.1 in this case row 3 is closer to being balanced than row 2, but the standard deviation is higher
4 35 23 17 25 7.5 row 4 has the same average as row 3, is not as balanced and has a higher standard deviation
5 255 250 245 250 4.1 in this case, row 5 has the same standard deviation as row 3, but is closer to being balanced
These numbers come from 3-phase power measurements (in Amps). Balancing them improves the overall utilization. I've tried other excel functions like estimated standard deviation, variance, average deviation, and deviation squares, but none give the answers that I want to rate the rows from most to least balanced.
![]()
One method that seems to work is to weight the results by dividing the standard deviation by the average. This gives the right info:
std dev.P / avg
Row 1 .16
2 .43 <- this series is the least balanced
3 .16
4 .30
5 .02 <- this series is the most balanced
But, I don't know if this is the right way to calculate the weighted deviation. Is this correct ? Is there an Excel formula I've missed ?
I have several weighted values for which I am taking a weighted average. I want to calculate a weighted standard deviation using the weighted values and weighted average. How would I modify the typical standard deviation to include weights on each measurement?
This is the standard deviation formula I am using.
When I simply use each weighted value for 'x' and the weighted average for 'bar{x}', the result seems smaller than it should be.
Steven C. HowellSteven C. Howell
4,82866 gold badges4040 silver badges6464 bronze badges
1 Answer
I just found this wikipedia page discussing data of equal significance vs weighted data. The correct way to calculate the biased weighted estimator of variance is
,
though the following, on-the-fly implementation, is more efficient computationally as it does not require calculating the weighted average before looping over the sum on the weighted differences squared
.
Despite my skepticism, I tried both and got the exact same results.
Note, be sure to use the weighted average
.
Steven C. HowellSteven C. Howell
![]()
4,82866 gold badges4040 silver badges6464 bronze badges
Not the answer you're looking for? Browse other questions tagged statisticsstandard-deviationweighted-average or ask your own question.Comments are closed.
|
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |