Sub MSD_Calc_blinks() ' ' Macro1 Macro ' Macro recorded 14/05/2008 by Neil Kad ' ' Range("A1").Select ActiveCell(1, 2).Formula = "=COUNT(c:c)" no = ActiveCell(1, 2).Value ActiveCell(no + 7, 1) = "Results" Range(Cells(1, 4), Cells(no + 3, no + 5)).ClearContents For y = 0 To no pos = 5: av = 0: blink = 0 ActiveCell(y + no + 10, 1) = ActiveCell(y + pos - 1, 1).Value While ActiveCell(pos + y, 3) <> "" If ActiveCell(pos + y, 2) = "" Or ActiveCell(pos - 1, 2) = "" Then 'check for blinks current = 0 GoSub blink Else current = (ActiveCell(pos + y, 2) - ActiveCell(pos - 1, 2)) ^ 2 + (ActiveCell(pos + y, 3) - ActiveCell(pos - 1, 3)) ^ 2 ' squared disp in 2D ActiveCell(pos, 5 + y) = current End If pos = pos + 1 ' pos is simply used to move down the data stream av = current + av 'adds up numbers for sum that is used for mean calc below Wend If (pos - 5) >= 1 Then ActiveCell(no + y + 10, 2) = av / (pos - 5 - blink) 'puts mean value into table below data Next y Exit Sub blink: 'this routine puts in spaces where numbers should be and also increments the blink counter ' the blink counter is used to amend the mean calcuation ActiveCell(pos, 5 + y) = "" blink = blink + 1 Return End Sub