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


wrote in message
...
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