Posted By

BDOGG32 on 04/14/15


Tagged

code function excel vba average


Versions (?)

Who likes this?

1 person have marked this snippet as a favorite

BDOGG32


Calculate the Center Moving Average


 / Published in: Visual Basic
 

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)

  1. Public Function CMA(vA As Variant, iSteps As Long) As Variant
  2.  
  3. Dim row As Long
  4. Dim iRows As Long
  5. Dim dblSum As Double
  6. Dim vAVG As Variant
  7.  
  8. On Error GoTo ErrorHandler
  9.  
  10. vA = vA.Value2
  11. iRows = UBound(vA)
  12. ReDim vAVG(iSteps To iRows, 1 To 1)
  13.  
  14. For row = 1 To iRows
  15. dblSum = dblSum + vA(row, 1)
  16. If row >= iSteps Then
  17. If row > iSteps Then dblSum = dblSum - vA(row - iSteps, 1)
  18. vAVG(row, 1) = dblSum / iSteps
  19. End If
  20. Next
  21.  
  22. 'If Not iSteps Mod 2 Then
  23. ' Daniel Says: This was my mistake.
  24. ' The above line of code uses logic that
  25. ' we would use with worksheet formulas.
  26. ' The below line of code is the way I
  27. ' should have written it for VBA.
  28. If iSteps Mod 2 = 0 Then
  29. For row = iSteps To iRows - 1
  30. vAVG(row, 1) = (vAVG(row, 1) + vAVG(row + 1, 1)) / 2
  31. Next
  32. vAVG(row, 1) = (vAVG(row, 1) * iSteps * 2 - vA(row - iSteps + 1, 1)) / (iSteps * 2 - 1)
  33. End If
  34.  
  35. CMA = vAVG
  36.  
  37. Exit Function
  38. ErrorHandler:
  39. CMA = CVErr(xlErrValue)
  40.  
  41. End Function

Report this snippet  

You need to login to post a comment.