Results 1 to 7 of 7

Thread: Weighted Standard Deviation

  1. #1

    Weighted Standard Deviation



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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
    Attached Files Attached Files

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,491
    Articles
    0
    Excel Version
    Excel 2016
    How do you know that the green column is correct?

    Your formulas seem fine as far as I can see....


  3. #3
    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.

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,491
    Articles
    0
    Excel Version
    Excel 2016
    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....


  5. #5
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    778
    Articles
    0
    Excel Version
    2010
    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-prog...deviation.html

    HTH
    Hercules

  6. #6
    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

  7. #7
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    778
    Articles
    0
    Excel Version
    2010
    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
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •