Using Collect to Get Average from Previous Year Data
Hi,
I'm trying to get an average using collect where the data was stamped with a date from last year:
=IFERROR(AVG(COLLECT({GIWO_Completed_2023 Range 3},{GIWO_Completed_2023 Range 5}, DATE(YEAR(TODAY())-1),{GIWO_Completed_2023 Range 2}, OR(@cell = "New Br4 (Frankfurt)", @cell = "Existing Br4 (Frankfurt)"))), "-"))
If you could help that would be great.
TKs,
Mike
Answers
-
Two adjustments that you'd need to make.
{GIWO_Completed_2023 Range 5}, DATE(YEAR(TODAY())-1)
You want to make sure that each cell in that range is last year. So your logic would be YEAR(@cell)=YEAR(TODAY())-1
The other is that dates can be weird with formulas sometimes. In this case, we have to tell the formula that we only want to look at the cells with dates (maybe this isn't an issue in your particular case), but if there are any blanks, you'd get a #INVALID DATA TYPE error. So you need to add into your COLLECT() function
{GIWO_Completed_2023 Range 5}, ISDATE(@cell)
So with those two changes, hopefully it will work something like this:
=IFERROR(AVG(COLLECT({GIWO_Completed_2023 Range 3}, {GIWO_Completed_2023 Range 5}, ISDATE(@cell), {GIWO_Completed_2023 Range 5}, YEAR(@cell)=YEAR(TODAY())-1, {GIWO_Completed_2023 Range 2}, OR(@cell = "New Br4 (Frankfurt)", @cell = "Existing Br4 (Frankfurt)"))), "-"))
Jason Tarpinian - Sevan Technology
Smartsheet Aligned Partner
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!