by Allen Wyatt
(last updated March 13, 2021)
David has a table with a couple of thousand rows. Column A contains dates, column B contains names, and column C contains numbers. He needs to average the latest 10 numbers (column C) for a given name (column B) that occur before a given date (column A). He wonders if there is a way to do this in a single formula, or if he needs to use intermediate values in helper columns. The sticking part, for David, is figuring out the last 10 numbers that occur chronologically before a specified date.
If you just wanted to find the latest chronological dates for a given name, then you could use the AVERAGEIFS function, which is relatively simple:
This formula assumes that you have data in the range A2:C7870, configured as David suggests. It also relies on a person's name entered into cell E1. It returns an average of the last 10 values for that person.
David, however, wanted an average of the last 10 values for that person prior to a specified cutoff date. The easiest way to figure that using a formula is to rely on the LET function, as shown here:
=LET(Dates, $A$1:$A$7870, Names, $B$1:$B$7870, Amounts, $C$1:$C$7870, LookupName, $E$1, LookupDate, $F$1, HowMany, $G$1, DateTest, FILTER(Amounts, Dates <= LookupDate), NameTest, FILTER(Names, Dates <= LookupDate), AmountList, FILTER(DateTest, NameTest = LookupName), AVERAGE(INDEX(AmountList, SEQUENCE(HowMany, 1, COUNT(AmountList), -1) ) ) )
It is more complex than the earlier AVERAGEIFS-based formula, but can be understood with not too much difficulty. The majority of the LET function's parameters are used to define names and values , in this manner:
Dates, $A$1:$A$7870, Names, $B$1:$B$7870, Amounts, $C$1:$C$7870, LookupName, $E$1, LookupDate, $F$1, HowMany, $G$1, DateTest, FILTER(Amounts, Dates <= LookupDate), NameTest, FILTER(Names, Dates <= LookupDate), AmountList, FILTER(DateTest, NameTest = LookupName)
Each of these is what Excel refers to as a name/value pair. For instance, Dates is the name and $A$1:$A$7870 is the cell range assigned to that name. Note that each successive name/value pair can rely on names defined earlier, as in NameTest being defined as the values returned by FILTER(Names, Dates <= LookupDate). Each of these names can then be used in the next portion of the function:
AVERAGE(INDEX(AmountList, SEQUENCE(HowMany, 1, COUNT(AmountList), -1) ) )
This is what actually returns the average. In order for the formula to work, there are three parameters that need to be set. The first is cell E1 which contains the name of the desired person, the second is cell F1 which contains the cutoff date, and the third is cell G1 which contains how many values you want to average.
If you prefer, you could also use sorting and filtering to get the desired information. Follow these general steps:
Make sure you replace Range with the cell range for the last 10 visible rows. For instance, in my testing data this ended up being the range C7737:C7797. (I had to manually determine the range by counting the number of unfiltered rows.) The SUBTOTAL function using the parameter 101 returns the average of the unfiltered rows in that cell range.
If you would prefer a macro-based solution, this one is rather handy:
Function AvgNameBeforeDate(rRaw As Range, sName As String, dCutOff As Date) As Double Dim rNames As Range Dim rDates As Range Dim rNums As Range Dim dblAccum As Double Dim iCnt As Integer Set rDates = rRaw.Columns(1) Set rNames = rRaw.Columns(2) Set rNums = rRaw.Columns(3) ' Sort the data ActiveSheet.Sort.SortFields.Clear ActiveSheet.Sort.SortFields.Add2 Key:=rNames, SortOn:=xlSortOnValues, _ Order:=xlAscending, DataOption:=xlSortNormal ActiveSheet.Sort.SortFields.Add2 Key:=rDates, SortOn:=xlSortOnValues, _ Order:=xlAscending, DataOption:=xlSortNormal With ActiveSheet.Sort .SetRange rRaw .Header = xlNo .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With ' Step backward through the sorted data dblAccum = 0 iCnt = 0 For J = rNames.Rows.Count To 1 Step -1 If rNames.Cells(J) = sName Then ' Found the desired name If rDates.Cells(J) < dCutOff Then ' Before the cutoff date dblAccum = dblAccum + rNums.Cells(J) iCnt = iCnt + 1 If iCnt = 10 Then ' Have now accumulated 10 values ' So calculate the average and exit AvgNameBeforeDate = dblAccum / iCnt Exit Function End If End If End If Next J ' Return 0 because never got to 10 values to average AvgNameBeforeDate = 0 End Function
This is a user-defined function, so you would use it in the worksheet in this manner:
Note that there are three parameters: the range to be analyzed (without any header rows), the name you want used, and the cutoff date. Be aware that the function sorts your data (what you specify in the first parameter), so there is an effect on that data. If you don't want your data sorted, then you should rely on the earlier LET formula, instead.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13834) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365.
Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!
If you define your named ranges after you create your formulas, you can have Excel update those formulas to reflect the ...Discover More
When analyzing data, you may need to distill groupings from that data. This tip examines how you can use formulas and ...Discover More
Do you need to total all the cells that are a particular color, such as yellow? This tip looks at three different ways ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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.