Weighted Standard Deviation

dhazi

New member
Joined
Mar 20, 2013
Messages
10
Reaction score
0
Points
0
Hi guys,

I have been trying to calculate the weighted standard deviation for some data series but do not seem to get it right - the result is apparently somewhat off the mark. It would be really great if I could get some help on this - I have attached a spreadsheet that, the result in the red column is mine and the one in the green is the one that should be matched, I guess it was calculated in Crystal Ball or something like that, the yellow column is for the would be new formula).

Many thanks.

David
 

Attachments

  • Weighted SD.xlsm
    416.4 KB · Views: 367
How do you know that the green column is correct?

Your formulas seem fine as far as I can see....
 
Thanks for checking...I don't, I just got back the new figures after I had submitted mines - no calculation, just hard numbers.

Anyway, I had been searching the net for some time for answers and was rechecking my calculations again and again before I have posted on the forum...but I guess next time I will start here).

Once again, thanks very much for your time, thanks for your help.
 
No problem.

I am not an expert on weighted averages or any other statistics really.... so I am not positive that your structure is absolutely correct... but they seem fine based on what I have seen on weighted standard deviations....
 
Hi dhazi
Im not a maths expert, but it looks like Excel doesn't have a function for WSD, which is why you've built your own formula. I noticed that you sent the attachment at .xlsm, so you might have some VBA experience.
I came across this post from someone who designed a function from scratch. Its part of response #6. You could try copying it into a spare VBA module and play around with it.

http://www.excelforum.com/excel-programming-vba-macros/486545-weighted-standard-deviation.html

HTH
Hercules
 
Hercules:

Thanks for your suggestion but that code is somewhat beyond me...somewhat, ha!...I wasn't even 100% sure if I was using the right syntax for my own formula - but never mind, one day I will get there for sure.

Cheers,

David
 
Dhazi
Sorry Ive been a bit tied up. Ive added this new function to your example, and it does produce results. Its available as a normal function, apart from the fact that it does have any supporting help because its user defined. Its called WTAVG and it requires 2 parameters

Weight Range and Data Range

Ive tried a couple of examples in cols X and AC, but Im probably not defining the ranges correctly. Perhaps if you experiment with some small amount of data then you can determine if its any help.


Hercules
 

Attachments

  • Weighted SD.xlsm
    434.6 KB · Views: 327
Back
Top