Average Based on 2 Separate Criteria

Options

I'm trying to create a formula that will average the age of an event if it meets two separate criteria:
1. The event occurred in that month (date of notification)
2. The event status is "Complete"

The age of each event is already calculated for each event in a separate column. I'm getting a syntax error with the formula below:

=IFERROR(AVERAGEIF({Date of Notification}, AND(({Date of Notification}, AND(IFERROR(MONTH(@cell), 0) = MONTH([Start Date]@row), IFERROR(YEAR(@cell), 0) = YEAR([Start Date]@row))), {Status} = "Complete"), {Age}), "").

I have a similar formula that does work where it's calculating the age for all events that occurred that month:

=IFERROR(AVERAGEIF({Date of Notification}, AND(IFERROR(MONTH(@cell), 0) = MONTH([Start Date]@row), IFERROR(YEAR(@cell), 0) = YEAR([Start Date]@row)), {Age}), "")

Tags:

Answers

  • Brian_Richardson
    Brian_Richardson Overachievers Alumni
    edited 06/19/24
    Options

    It's much cleaner to use COLLECT to get the results you want, then average them, since there's no AVERAGEIFS function.

    Below, COLLECT is creating a temporary array of the Age values for any row that meets the criteria:

    • Date of Notification month is the same as Start Date month
    • Date of Notification year is the same as Start Date year
    • Status is Complete

    AVERAGE then averages the collection of Age values that met those criteria.

    And you already have the IFERROR to blank out if nothing is found

    = IFERROR( AVERAGE( COLLECT( {Age}, {Date of Notification}, MONTH(@cell)=MONTH([Start Date]@row, {Date of Notification}, YEAR(@cell)=YEAR([Start Date]@row, {Status}, "Complete")), "")

    BRIAN RICHARDSON | PMO TOOLS AND RESOURCES | HE|HIM

    SEATTLE WA, USA

    IRON MOUNTAIN