# Dynamic year formula for the previous year

Options
✭✭✭✭

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?

Tags:

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭
Options

Thank you very much! It works!

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!