UDF Function to calculate mean moving range (control charts)

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
Thu May 08 07:31:20 CDT 2008

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


<sharon_hutchison@standardlife.com> wrote in message
news:b2335147-ad45-4b04-b48c-703355ebf6e0@b1g2000hsg.googlegroups.com...
>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

Joel
Thu May 08 07:54:00 CDT 2008

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


"sharon_hutchison@standardlife.com" 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
>

Bernie
Thu May 08 08:12:16 CDT 2008

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" <Joel@discussions.microsoft.com> wrote in message
news:37B95742-DE6C-4218-B1AF-4BDD1A1209AF@microsoft.com...
> 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
>
>
> "sharon_hutchison@standardlife.com" 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
>>