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?

Find more posts tagged with

Formulas

Accepted answers

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

All comments

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.