I am working with an Excel Workbook that is used to record measurements. Each Sheet is a copy of the master for a new lot to record measurements for that lot. I am trying to automatically calculate the Cpk the same way Minitab does and unfortunately Minitab doesn't use a normal standard deviation, it uses Stdev(within). I need help figuring out why my code isn't working becuase it appears so simple but it isn't summing the loop correctly.
The way this workbook is set-up, no matter how many results there are I can sum them simply with the code =Sum(Inspection_Record3) and this will sum all the results under the 1 column. I could do the same thing for an average,a count, etc. In an outside cell I use the count function so then in my code I can refer to that count, subtract two and I have all the values I need to use to get my absolute values. The issue is to get the stdev(within) I need to find the moving range in the background. To do this you take the absolute value of the first result minus the second result, add that to the absolute value of the second minus the third, and so on for all the results and get the sum of all these numbers. I can't seem to make this work at all. I thought I knew how to get it and when I hit play in VBA it scrolls down to all the proper cells and stops where it is supposed to but when I try typing the function =josh() in a cell it says "Microsoft Excel cannot calculate a formula. There is a circular reference in an open workbook, but the references that cause it cannot be listed for you. Try editing the last formula you entered or removing it with the Undo command". Then it just puts 0 in the cell. I swear I have done loops like this before where the sum in the loop just keeps growing until the loop is over. I have no idea why this isn't working. Any help would be soooo greatly appreciated. I know it is something small I'm missing I just can't seem to see it. My code is below.
Thanks Everyone who took the time to read this and thanks in advance for any assistance you may be able to provide.
1 Function josh() 2 Dim x As Integer 3 ' Set numrows = number of rows of data. 4 NumR = Range("M7").Value 5 NumRows = NumR - 2 6 ' Select cell F17. 7 Range("F17").Select 8 ' Establish "For" loop to loop "numrows" number of times. 9 For x = 1 To NumRows 10 Y = ActiveCell.Value 11 ActiveCell.Offset(1, 0).Select 12 Z = ActiveCell.Value 13 Q = Abs(Z - Y) 14 Sum = Sum + Q 15 Next 16 josh = Sum 17 18 End Function
Thanks again everyone!