Previous Topic Next Topic

Macro to check the last 7 values against mean

Closed
Colin - Jun 28, 2011 at 08:49 AM
RWomanizer
Posts
365
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
 - Jun 30, 2011 at 02:25 AM
Hello,

I am looking for help writing a macro to check the values of my last set of 7 results against a mean of the last 30 points.

-Say I have 40 values in A1:A40...

I would like a macro to check the last 7 results entered (A40 to A[40-7]) and compare them to a mean calculated from the last 30 results.
If all 7 values all fall above or below the mean in succession, I would like to highlight the 7th value.

I would also like the macro to update, so that as I add more values to the column, it continually calculates a mean of the last 30 points, checks to see if the last 7 points are all greater than or less than the mean, and highlights the 7th value above or below the mean.

Any solutions?

1 reply

Answer 1 / 1
RWomanizer
Posts
365
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
 120
Jun 30, 2011 at 02:25 AM
here the code is: 

Private Sub CondFormat()
Dim lastrow, i As Long
Dim Mean As Long

lastrow = Range("A" & Rows.Count).End(xlUp).Row

Mean = WorksheetFunction.Average(Range("A" & lastrow - 29, "A" & lastrow))

For i = lastrow - 6 To lastrow
    If Range("A" & i) < Mean Then
        Range("A" & i).Interior.ColorIndex = 6
        Else
        Range("A" & i).Interior.ColorIndex = 10
    End If
Next i

End Sub

Its color last seven value as yellow if less than mean and Green if greater than mean. and no formating if equal to mean. :)
0