Highlighting an Unchanging Value

Bonnie has a worksheet used to accumulate data from testing equipment. Readings from the equipment are added to the bottom of column A daily. In cell D4 Bonnie has a formula that calculates the average of the last 14 readings contained in column A. She wonders if there is a way to highlight cell D4 or flag the value it contains if the average hasn't changed over the past week.

There are a couple of ways this could be handled. The key is to understand that the only way to make this determination is to keep track of 7 14-day averages, one for each day of the past week. This can obviously only be done if you can change the layout of your worksheet so that D4 is only the most recent 14-day average. Perhaps, in cells D5:D10 you could place the 14-day averages for each of the preceding 6 days.

As an example, let's say that you are using Excel 365 and generating the current 14-day average, stored in cell D4, with the following formula:

=AVERAGE(TAKE(TRIMRANGE(A:A,2),-14))

In cell D5 you want the 14-day average for the period ending on the previous day, so you would add the DROP function to the formula, in this manner:

=AVERAGE(TAKE(DROP(TRIMRANGE(A:A,2),-1),-14))

The key here is that you are specifying, through the -1 parameter, that the last day's reading be dropped from the range of cells being considered. Thus, in cells D6, D7, D8, D9, and D10 you could use parameters of -2, -3, -4, -5, and -6. What you end up with in the range D4:D10 is the last 7 days of 14-day averages. You can then easily compare these to see if they are the same, or you can use a conditional formatting rule to highlight the cells if they are not equal.

If you are not using Excel 365, you may be better off using a macro to do the necessary calculations. Assuming that the data in column A contains no blank cells, you can use the following formula for your latest average in cell D4:

=AVERAGE(OFFSET(A1, COUNTA(A:A) - 14, 0, 14))

The following macro will perform this same formula on your A:A data, but also look at the previous six days' averages:

Function SameAvgs() As Boolean
    Const EVAL1 = "AVERAGE(OFFSET(A1, COUNTA(A:A) - "
    Const EVAL2 = ", 0, 14))"
    Dim n As Integer
    Dim xAvg As Double

    Application.Volatile

    ' Default return is that the averages are the same
    SameAvgs = True

    ' Calculate the 14-day average for today
    xAvg = Evaluate(EVAL1 & 14 & EVAL2)

    ' Step through the averages for the previous 6 days
    For n = 1 To 6
        If xAvg <> Evaluate(EVAL1 & (14 + n) & EVAL2) Then
            ' Averages are not the same
            SameAvgs = False
        End If
    Next n
End Function

The function returns True or False, depending on whether the 7 days of averages are the same or not. You can put this formula in its own cell (perhaps E4) in order to flag the value, as Bonnie wants:

=IF(SameAvgs(),"No Change in 7 Days!","")

You could also use the function in a conditional formatting rule to highlight cell D4 if the function returns True, meaning that there has been no change in the average over the 7-day period.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13957) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365.