UDF Function to calculate mean moving range (control charts)
Joel,
Your code was missing a few key points (like the End If), and a few changes that would improve it.
HTH,
Bernie
MS Excel MVP
Function MMR(CalledCells As Range) As Variant
Application.Volatile True
Dim First As Boolean
Dim Cell As Range
Dim Previous As Variant
First = True
For Each Cell In CalledCells
If First Then
Previous = Cell.Value
First = False
MMR = 0
Else
MMR = MMR + Abs(Cell.Value - Previous)
Previous = Cell.Value
End If
Next Cell
MMR = MMR / IIf(CalledCells.Cells.Count 1, CalledCells.Cells.Count - 1, 1)
End Function
"Joel" wrote in message
...
Function MMR(CalledCells As range) As Variant
Application.Volatile True
Dim V1 As Variant
Dim V2 As Variant
Dim V3 As Variant
Dim i As Integer
Dim FirstRow As Long
Dim LastRow As Long
V1 = 0
first = true
for each cell in CalledCells
if first = true then
previous = cell
first = false
else
V2 = Abs(cell - Previous))
V3 = V2 + V1
V1 = V3
previous = cell
Next cell
MMR = V1 / (CalledCells.Rows.Count - 1)
End Function
" wrote:
I was about to post a question as I was trying to create a function
that would calculate the moving mean range which I would then be able
to use to calculate the UCL and LCL for a series of data i.e
Mean + 2.66*Moving mean range, without having to calculate all of the
variances between each 2 sets of data....hope that makes sense.
However, I have cracked it and wanted to post the Function in case
anyone else wants it:
It's pretty simple stuff as it is the first user defined function I've
written so apologies if this is clumsy code....
Function MMR(CalledCells As range) As Variant
Application.Volatile True
Dim V1 As Variant
Dim V2 As Variant
Dim V3 As Variant
Dim i As Integer
Dim FirstRow As Long
Dim LastRow As Long
V1 = 0
FirstRow = CalledCells.Row
LastRow = CalledCells.Row + CalledCells.Rows.Count - 1
For i = LastRow To FirstRow + 1 Step -1
V2 = Abs(Cells(i, CalledCells.Column).Value - Cells(i,
CalledCells.Column).Offset(-1, 0).Value)
V3 = V2 + V1
V1 = V3
Next i
MMR = V1 / (CalledCells.Rows.Count - 1)
End Function
|