UDF Function to calculate mean moving range (control charts)

[sharon_hutchison]

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

 

[Bernie Deitrick]

Instead of

=MMR(B1:B14)

use the Array function (Enter using Ctrl-Shift-Enter)

=AVERAGE(ABS(B2:B14-B1:B13))

Note the 1 cell offset of the two ranges.

HTH,
Bernie
MS Excel MVP

 

[Joel]

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

 

[Bernie Deitrick]

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