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

  • Jason Tarpinian
    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)"))), "-"))

    Jason Tarpinian - Sevan Technology

    Smartsheet Aligned Partner

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!