macro to check the last 7 values against mean Closed

Question

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?

Configuration: Windows XP / Firefox 5.0

RWomanizer · Answer

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. :)

Macro to check the last 7 values against mean

1 response

Similar discussions

Subscribe To Our Newsletter!