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


--
bondcrash
------------------------------------------------------------------------
bondcrash's Profile: http://www.excelforum.com/member.php...o&userid=20997
View this thread: http://www.excelforum.com/showthread...hreadid=486545

Weighted Standard Deviation
 

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


"bondcrash" wrote in
message ...

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

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


--
bondcrash
------------------------------------------------------------------------
bondcrash's Profile: http://www.excelforum.com/member.php...o&userid=20997
View this thread: http://www.excelforum.com/showthread...hreadid=486545

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" wrote
in message ...

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

Weighted Standard Deviation
 

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


--
bondcrash
------------------------------------------------------------------------
bondcrash's Profile: http://www.excelforum.com/member.php...o&userid=20997
View this thread: http://www.excelforum.com/showthread...hreadid=486545

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" wrote in
message ...

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


--
bondcrash

Weighted Standard Deviation
 

thats very cool. tks a lot Jon.

BC


--
bondcrash
------------------------------------------------------------------------
bondcrash's Profile: http://www.excelforum.com/member.php...o&userid=20997
View this thread: http://www.excelforum.com/showthread...hreadid=486545