Averaging Based on Multiple Criteria

Written by Allen Wyatt (last updated March 13, 2021)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and 2021


3

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:

=AVERAGEIFS($C$2:$C$7870,$B$2:$B$7870,$E$1,$A$2:$A$7870,">="&LARGE(IF($B$2:$B$7870=E$1,$A$2:$A$7870,"na"),10))

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:

  1. If you have not done so already, format your data as a table. (Select a cell in your data and click Format As Table on the Home tab of the ribbon.)
  2. Sort the Date column in ascending order and filter it to show only those dates before your cutoff date.
  3. Filter the Name column to show only those records for the name desired.
  4. Enter this formula in a cell below the last record in column C:
=SUBTOTAL(101, Range)

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:

=AvgNameBeforeDate(A2:C7870,"Frank","1/1/2021")

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.

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

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

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Selecting Printing of Color Pictures

Do you want to control whether color pictures in your document are printed or not? It's not quite as easy as it may ...

Discover More

Counting Precedents and Dependents

Do you need to know how many precedents or dependents there are on a worksheet? You could count them manually, or you ...

Discover More

Dynamic Headers and Footers

Do you want to change the headers and footers that appear on different pages of your printout? Here's how you can get ...

Discover More

Best-Selling VBA Tutorial for Beginners Take your Excel knowledge to the next level. With a little background in VBA programming, you can go well beyond basic spreadsheets and functions. Use macros to reduce errors, save time, and integrate with other Microsoft applications. Fully updated for the latest version of Office 365. Check out Microsoft 365 Excel VBA Programming For Dummies today!

More ExcelTips (ribbon)

Calculating the Interval between Occurrences

With a long list of items in a worksheet, you may want to determine the last time a particular item appeared in the list. ...

Discover More

Determining If a Value is Out of Limits

Need to figure out if a value is outside of some arbitrary limit related to a different value? There are a number of ways ...

Discover More

Getting a Conditional Count of Cells Containing Values

Excel provides several worksheet functions that can be used to count cells containing values--"particularly numeric ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is seven more than 2?

2021-12-16 04:01:02

Philip

@Mike, @Peter, to my knowledge these were introduced more or less simultaneously with the new "Dynamic Array Functions" (such as XLOOKUP), and are for Office 365 and Excel 2021 only ...


2021-03-24 14:44:04

Mike

Ditto Peter's question about Let(). I'm using Excel from Microsoft Office Professional Plus 2016 (on Windows 10) and it isn't there. Is this another Office360 only function?


2021-03-13 18:52:37

Peter

Wow. When did Let() and Sequence() appear? I'm sure I can find a use for them.


This Site

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.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.