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

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
>

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

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
>

cheers Jon - i have done it with formulas but i am trying to build a function to make life easier

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

thats very cool. tks a lot Jon.

BC

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