Average Based on 2 Separate Criteria
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}), "")
Best Answer
-
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")), "")
Answers
-
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")), "")
-
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")), "")
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!