Dynamic year formula for the previous year

Anna Makhina

Hello!

I would like to show the average score for the previous year without indicating specific year (instead of showing 2023 I use formula (YEAR(TODAY()) – 1.

=AVG(COLLECT({Vendor Evaluation Sheet - Archive - Score}, {Vendor Evaluation Sheet - Task ID}, [Task ID]@row, {Vendor Evaluation Sheet - Archive - Created}, >=DATE(YEAR(TODAY()) – 1, 1, 1), {Vendor Evaluation Sheet - Archive - Created}, <=DATE(YEAR(TODAY()) – 1, 12, 31)))

error message: #INVALID OPERATION

Would you please correct the formula?

KPH

There is something unusual about your minus signs. The formula looks good but the minus signs look too long. They are longer than the hyphen in the cross sheet reference.

I don't usually suggest pasting from here into smartsheet, but maybe try this? Or try just deleting and replacing the minus signs.

=AVG(COLLECT({Vendor Evaluation Sheet - Archive - Score}, {Vendor Evaluation Sheet - Task ID}, [Task ID]@row, {Vendor Evaluation Sheet - Archive - Created}, >=DATE(YEAR(TODAY()) - 1, 1, 1),  {Vendor Evaluation Sheet - Archive - Created}, <=DATE(YEAR(TODAY()) - 1, 12, 31)))

KPH

There is something unusual about your minus signs. The formula looks good but the minus signs look too long. They are longer than the hyphen in the cross sheet reference.

I don't usually suggest pasting from here into smartsheet, but maybe try this? Or try just deleting and replacing the minus signs.

=AVG(COLLECT({Vendor Evaluation Sheet - Archive - Score}, {Vendor Evaluation Sheet - Task ID}, [Task ID]@row, {Vendor Evaluation Sheet - Archive - Created}, >=DATE(YEAR(TODAY()) - 1, 1, 1),  {Vendor Evaluation Sheet - Archive - Created}, <=DATE(YEAR(TODAY()) - 1, 12, 31)))

Anna Makhina

Thank you very much! It works!

KPH

That's great. Do you know what caused the long minus signs? (in case it comes up again)

Anna Makhina

In my experience, some symbols (e.g. minus, quotes) may change when I copy formulas and paste to Outlook. Its better to use Windows Notepad.

KPH

Oh yes, I would definitely not recommend copying and pasting formulas into anything like outlook or word.