Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Standard deviation | Excel Discussion (Misc queries) | |||
standard deviation | Excel Worksheet Functions | |||
Standard Deviation | Excel Worksheet Functions | |||
Standard Deviation | Excel Worksheet Functions | |||
Weighted Average Standard Deviation | Excel Worksheet Functions |