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?

Tags:

Best Answer

  • KPH
    KPH ✭✭✭✭✭✭
    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

  • KPH
    KPH ✭✭✭✭✭✭
    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)))

  • Anna Makhina
    Anna Makhina ✭✭✭✭

    Thank you very much! It works!

  • KPH
    KPH ✭✭✭✭✭✭

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

  • Anna Makhina
    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
    KPH ✭✭✭✭✭✭

    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!