Average Based on 2 Separate Criteria

kss5229

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}), "")

kss5229

Thanks, with some tweaks to the formula you provided, I was able to get it to work!

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

Brian_Richardson

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")), "")

kss5229

Thanks, with some tweaks to the formula you provided, I was able to get it to work!

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