Written by Allen Wyatt (last updated September 20, 2025)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365
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.
Note:
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.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 365 applications with VBA programming. Written in clear terms and understandable language, the book includes systematic tutorials and contains both intermediate and advanced content for experienced VB developers. Designed to be comprehensive, the book addresses not just one Office application, but the entire Office suite. Check out Mastering VBA for Microsoft Office 365 today!
Want to figure out how far it is between two points on the globe? If you know the points by latitude and longitude, you ...
Discover MoreIf you have a list of names in a column, and you want to separate those names into individual cells, there are several ...
Discover MoreDo you need to total all the cells that are a particular color, such as yellow? This tip looks at three different ways ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2025-09-21 17:06:10
J. Woolley
TRIMRANGE(A:A, 2) is an Excel 365 function that returns the range A:A down to and including the last non-blank cell; empty cells after the last non-blank cell are not included (trimmed). But Excel 365 also includes the dot operator (.) that trims a range when used with the colon operator (:), so A:.A returns the same result.
COUNTA(A:A) returns the row number of the last data value in column A, but only if "the data in column A contains no blank cells" (beginning with cell A1) and all cells after the last data value are empty.
Therefore, these 3 expressions return the same result, but the first 2 currently require Excel 365:
TAKE(TRIMRANGE(A:A, 2), -14)
TAKE(A:.A, -14)
OFFSET(A1, COUNTA(A:A) - 14, 0, 14)
2025-09-20 10:38:44
J. Woolley
Re. Function SameAvgs(), I suggest adding 'Exit Function' before 'End If'.
But here's another way (which also avoids Application.Volatile):
Function SameAvgs(xAvg As Double) As Boolean
Const EVAL1 = "AVERAGE(OFFSET(A1, COUNTA(A:A) - "
Const EVAL2 = ", 0, 14))"
Dim n As Integer
SameAvgs = False
For n = 1 To 6
If xAvg <> Evaluate(EVAL1 & (14 + n) & EVAL2) Then Exit Function
Next n
SameAvgs = True
End Function
Then use this formula:
=IF(SameAvgs($D$4),"No Change in 7 Days!","")
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Comments