+ Reply to Thread
Results 1 to 8 of 8

Weighted Standard Deviation

  1. #1
    Registered User
    Join Date
    03-11-2005
    Posts
    87

    Weighted Standard Deviation

    I am trying to have a measure of Standard Deviation where the different data are given each a certain weight. Is there any add-inn out there to do that?

    Many thanks

    BC

  2. #2
    Barb Reinhardt
    Guest

    Re: Weighted Standard Deviation

    Are you talking about using a pooled standard deviation? How are you
    determining the weights?


    "bondcrash" <bondcrash.1yr83m_1132421102.766@excelforum-nospam.com> wrote in
    message news:bondcrash.1yr83m_1132421102.766@excelforum-nospam.com...
    >
    > I am trying to have a measure of Standard Deviation where the different
    > data are given each a certain weight. Is there any add-inn out there to
    > do that?
    >
    > Many thanks
    >
    > BC
    >
    >
    > --
    > bondcrash
    > ------------------------------------------------------------------------
    > bondcrash's Profile:
    > http://www.excelforum.com/member.php...o&userid=20997
    > View this thread: http://www.excelforum.com/showthread...hreadid=486545
    >




  3. #3
    Registered User
    Join Date
    03-11-2005
    Posts
    87

    Weighted Standard Deviation

    i need to give different weights to 8 chrnological data - the higher to most recent data and so forth. sum of all the weights must be one

  4. #4
    Jon Peltier
    Guest

    Re: Weighted Standard Deviation

    If you Google for Weighted Standard Deviation, here's the first link:

    http://www.itl.nist.gov/div898/softw...2/weightsd.pdf

    - Jon
    -------
    Jon Peltier, Microsoft Excel MVP
    Peltier Technical Services
    Tutorials and Custom Solutions
    http://PeltierTech.com/
    _______


    "bondcrash" <bondcrash.1ysw1y_1132498803.0819@excelforum-nospam.com> wrote
    in message news:bondcrash.1ysw1y_1132498803.0819@excelforum-nospam.com...
    >
    > i need to give different weights to 8 chrnological data - the higher to
    > most recent data and so forth. sum of all the weights must be one
    >
    >
    > --
    > bondcrash
    > ------------------------------------------------------------------------
    > bondcrash's Profile:
    > http://www.excelforum.com/member.php...o&userid=20997
    > View this thread: http://www.excelforum.com/showthread...hreadid=486545
    >




  5. #5
    Registered User
    Join Date
    03-11-2005
    Posts
    87
    cheers Jon - i have done it with formulas but i am trying to build a function to make life easier

  6. #6
    Jon Peltier
    Guest

    Re: Weighted Standard Deviation

    Here are some UDFs I recently wrote, I believe using the source I cited.

    - Jon
    -------
    Jon Peltier, Microsoft Excel MVP
    Peltier Technical Services
    Tutorials and Custom Solutions
    http://PeltierTech.com/
    _______


    Option Explicit

    Public Function WtAvg(WeightRange As Range, DataRange As Range)
    '' Jon Peltier 20 June 2005
    '' Weighted Mean
    ''
    '' WeightRange may be any range of one column or one row.
    '' DataRange may be any range of one column or one row.
    ''
    '' Errors
    '' Range incorrectly sized: #NUM!
    '' Sum of weights = 0: #DIV/0!

    Dim dTest As Double

    If DataRange.Rows.Count <> 1 And DataRange.Columns.Count <> 1 Then
    '' Not a single row or column --> #NUM! ERROR
    WtAvg = CVErr(xlErrNum)
    Exit Function
    ElseIf WorksheetFunction.Count(DataRange) <> DataRange.Rows.Count *
    DataRange.Columns.Count Then
    '' Contains some blank or non-numeric cells --> #NUM! ERROR
    WtAvg = CVErr(xlErrNum)
    Exit Function
    ElseIf WeightRange.Rows.Count <> 1 And WeightRange.Columns.Count <> 1 Then
    '' Not a single row or column --> #NUM! ERROR
    WtAvg = CVErr(xlErrNum)
    Exit Function
    ElseIf WorksheetFunction.Count(WeightRange) <> WeightRange.Rows.Count *
    WeightRange.Columns.Count Then
    '' Contains some blank or non-numeric cells --> #NUM! ERROR
    WtAvg = CVErr(xlErrNum)
    Exit Function
    ElseIf WorksheetFunction.Count(WeightRange) <>
    WorksheetFunction.Count(DataRange) Then
    '' Unequal range sizes --> #NUM! ERROR
    WtAvg = CVErr(xlErrNum)
    Exit Function
    End If

    dTest = WorksheetFunction.SumProduct(WeightRange, DataRange)
    If WorksheetFunction.Sum(WeightRange) = 0 Then
    '' sum of weights is zero - division by zero
    WtAvg = CVErr(xlErrDiv0)
    Else
    WtAvg = dTest / WorksheetFunction.Sum(WeightRange)
    End If

    End Function

    Public Function WtStD(WeightRange As Range, DataRange As Range)
    '' Jon Peltier 20 June 2005
    '' Weighted Standard Deviation
    ''
    '' WeightRange may be any range of one column or one row.
    '' DataRange may be any range of one column or one row.
    ''
    '' Errors
    '' Range incorrectly sized: #NUM!
    '' Sum of weights = 0: #DIV/0!

    Dim dTest As Double
    Dim dSumWts As Double
    Dim dSumDatSq As Double
    Dim vWtMean As Variant
    Dim vrWeights As Variant
    Dim vrData As Variant
    Dim vWeights() As Double
    Dim vData() As Double
    Dim lCount As Long
    Dim lLoop As Long
    Dim lRow As Long
    Dim lCol As Long

    If DataRange.Rows.Count <> 1 And DataRange.Columns.Count <> 1 Then
    '' Not a single row or column --> #NUM! ERROR
    WtStD = CVErr(xlErrNum)
    Exit Function
    ElseIf WorksheetFunction.Count(DataRange) <> DataRange.Rows.Count *
    DataRange.Columns.Count Then
    '' Contains some blank or non-numeric cells --> #NUM! ERROR
    WtStD = CVErr(xlErrNum)
    Exit Function
    ElseIf WeightRange.Rows.Count <> 1 And WeightRange.Columns.Count <> 1 Then
    '' Not a single row or column --> #NUM! ERROR
    WtStD = CVErr(xlErrNum)
    Exit Function
    ElseIf WorksheetFunction.Count(WeightRange) <> WeightRange.Rows.Count *
    WeightRange.Columns.Count Then
    '' Contains some blank or non-numeric cells --> #NUM! ERROR
    WtStD = CVErr(xlErrNum)
    Exit Function
    ElseIf WorksheetFunction.Count(WeightRange) <>
    WorksheetFunction.Count(DataRange) Then
    '' Unequal range sizes --> #NUM! ERROR
    WtStD = CVErr(xlErrNum)
    Exit Function
    End If

    vWtMean = WtMean(WeightRange, DataRange)
    If IsNumeric(vWtMean) Then
    lCount = WorksheetFunction.Count(WeightRange)

    vrWeights = WeightRange.Value
    vrData = DataRange.Value

    ReDim vWeights(1 To lCount)
    ReDim vData(1 To lCount)

    For lRow = LBound(vrWeights, 1) To UBound(vrWeights, 1)
    For lCol = LBound(vrWeights, 2) To UBound(vrWeights, 2)
    vWeights(lRow * lCol) = vrWeights(lRow, lCol)
    Next
    Next

    For lRow = LBound(vrData, 1) To UBound(vrData, 1)
    For lCol = LBound(vrData, 2) To UBound(vrData, 2)
    vData(lRow * lCol) = vrData(lRow, lCol)
    Next
    Next

    dSumDatSq = 0
    dSumWts = 0
    For lLoop = 1 To lCount
    dSumDatSq = dSumDatSq + vWeights(lLoop) * (vData(lLoop) - vWtMean) ^ 2
    dSumWts = dSumWts + vWeights(lLoop)
    Next

    If dSumWts = 0 Then
    '' sum of weights is zero - division by zero
    WtStD = CVErr(xlErrDiv0)
    Exit Function
    End If

    dTest = dSumDatSq / dSumWts

    dTest = Sqr(dTest * dSumWts / (dSumWts - 1))

    WtStD = dTest
    Else

    End If
    End Function






    "bondcrash" <bondcrash.1yszra_1132503601.885@excelforum-nospam.com> wrote in
    message news:bondcrash.1yszra_1132503601.885@excelforum-nospam.com...
    >
    > cheers Jon - i have done it with formulas but i am trying to build a
    > function to make life easier
    >
    >
    > --
    > bondcrash




  7. #7
    Registered User
    Join Date
    03-11-2005
    Posts
    87
    thats very cool. tks a lot Jon.

    BC

  8. #8
    Registered User
    Join Date
    08-02-2010
    Location
    Denver, CO
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: Weighted Standard Deviation

    Hi Jon. Thanks for your code, very useful. I did however, notice a few errors.
    1.
    dTest = Sqr(dTest * dSumWts / (dSumWts - 1))
    should be
    dTest = Sqr(dTest * lCount / (lCount - 1))

    2.
    WtMean(WeightRange, DataRange)
    should be
    WtAvg(WeightRange, DataRange)

    I've testing the updated code and everything seems to work fine now. let me know if I'm wrong or missing something.

    Thanks again,
    Sean

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1