Using Collect to Get Average from Previous Year Data

Michael Foster

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

Jason Tarpinian

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