Dynamic year formula for the previous year
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?
Best Answer
-
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)))
Answers
-
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)))
-
Thank you very much! It works!
-
That's great. Do you know what caused the long minus signs? (in case it comes up again)
-
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.
-
Oh yes, I would definitely not recommend copying and pasting formulas into anything like outlook or word.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!