# Weighted Standard Deviation

1. ## 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  Register To Reply

2. ## 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
>  Register To Reply

3. ## 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  Register To Reply

4. ## Re: Weighted Standard Deviation

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
>  Register To Reply

5. cheers Jon - i have done it with formulas but i am trying to build a function to make life easier  Register To Reply

6. ## 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  Register To Reply

7. thats very cool. tks a lot Jon.

BC  Register To Reply

8. ## 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  Register To Reply