Advanced Search

Return to Snippet

Revision: 69070
at April 14, 2015 08:58 by BDOGG32
Initial Code 
Public Function CMA(vA As Variant, iSteps As Long) As Variant

    Dim row As Long
    Dim iRows As Long
    Dim dblSum As Double
    Dim vAVG As Variant
    
    On Error GoTo ErrorHandler
    
    vA = vA.Value2
    iRows = UBound(vA)
    ReDim vAVG(iSteps To iRows, 1 To 1)

    For row = 1 To iRows
        dblSum = dblSum + vA(row, 1)
        If row >= iSteps Then
            If row > iSteps Then dblSum = dblSum - vA(row - iSteps, 1)
            vAVG(row, 1) = dblSum / iSteps
        End If
    Next

    'If Not iSteps Mod 2 Then
    ' Daniel Says:  This was my mistake.
    ' The above line of code uses logic that
    ' we would use with worksheet formulas.
    ' The below line of code is the way I
    ' should have written it for VBA.
    If iSteps Mod 2 = 0 Then
        For row = iSteps To iRows - 1
            vAVG(row, 1) = (vAVG(row, 1) + vAVG(row + 1, 1)) / 2
        Next
        vAVG(row, 1) = (vAVG(row, 1) * iSteps * 2 - vA(row - iSteps + 1, 1)) / (iSteps * 2 - 1)
    End If

    CMA = vAVG

Exit Function
ErrorHandler:
    CMA = CVErr(xlErrValue)

End Function
Initial URL 

                                

                                                            

                                    

                                        

                                        Initial Description
                                        

                                    

                                    
Calculates the center moving average for a time period.  The vA requires a range or an array of numbers and the iSteps takes the number of periods in the timeframe (4 for quarters, 7 for weekly, ect)

                                

                                                            

                                    

                                        

                                        Initial Title
                                        

                                    

                                    
Calculate the Center Moving Average

                                

                                                            

                                    

                                        

                                        Initial Tags
                                        

                                    

                                    
code, function, excel

                                

                                                            

                                    

                                        

                                        Initial Language
                                        

                                    

                                    
Visual Basic