I need a formula to calculate a YTD average # of employees that will change as the year progresses.

barnhartd73091
edited 03/06/24 in Formulas and Functions

=AVERAGEIFS(Jan3:Dec3,">"&TODAY-365,Jan3:Dec3,"<"&Today) - this gives me the #UNPARSEABLE message

=AVG(Jan@row:Dec@row) / (MONTH(TODAY())) - this gives me another incorrect number

I have tried several others without success also. The number is manually entered each month and the total should change each month.

Tags:

Answers

  • Lucas Rayala
    Lucas Rayala ✭✭✭✭✭✭

    Hi @barnhartd73091,

    The row with the months doesn't have any dates, so you can't use date functions (i.e. TODAY, YEAR, etc) to reference them. If you are simply trying to exclude dates that haven't occurred, because the upcoming months will all have a zero, you can do this:

    =AVERAGEIFS(Jan3:Dec3, Jan3:Dec3, <>0)

    That's just asking it to average all the values that aren't equal to zero.

    Just a note, your formatting is a mismatch of Excel and Smartsheet, so even if you were referencing dates that could be recognized, the syntax wouldn't have been right. I would check out the Help & Learning, or just drop your formula into ChatGPT and tell it to convert it from Excel to Smartsheet syntax.

  • Thank you for your reply. I just put the formula into the sheet and still get the #UNPARSEABLE response. Any idea why?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!