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:

Best Answer

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

Answers

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

  • Anna Makhina
    Anna Makhina ✭✭✭✭
    Options

    Thank you very much! It works!

  • KPH
    KPH ✭✭✭✭✭✭
    Options

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

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

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