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

barnhartd73091

=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.

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.

barnhartd73091

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