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 |
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 |