Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF Function to calculate mean moving range (control charts)
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF Function to calculate mean moving range (control charts)
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Moving a Control on a Frame during Runtime | Excel Programming | |||
Button Control Moving | Excel Programming | |||
How can I calculate the Moving-Range without returning negatives | Excel Discussion (Misc queries) | |||
Prevent user moving to next control | Excel Programming | |||
Moving a control in a userform | Excel Programming |