AVERAGEIF returning 0
Hi all,
I'm trying to create an AVERAGEIF formula to achieve the following:
If the "start date" column shows a date from the previous month, then average the numbers in column "Q1"
No matter what I try to do, I keep producing the result of 0. Can anyone advise on how to do this?
I would have thought it would be something like:
AVERAGEIF([start date]:[start date], IFERROR(MONTH(@cell), 0) = IFERROR(MONTH(TODAY())  1, 12), [Q1]:[Q1]
Best Answer

Notice how your Q1  Q5 values are all on the left side of the columns? That means they're being treated as text. So you have to convert them into numeric values before you can perform math functions on them. Whatever formula you are using to get those values, wrap it in the VALUE function to convert the result to a numeric value:
=VALUE(<put your formula here>)
Regards,
Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Answers

I would build a column to calculate the month of your start date: =month([start date]@row)
Then I would use: =AVG(COLLECT([Q1]26:[Q1]34, Month26:Month34, (MONTH(TODAY())  1)))
where Month is the calculated month of the dates you are watching.
=AVERAGEIF(Month26:Month34, (MONTH(TODAY())  1), [Q1]26:[Q1]34) produces the same result.
I am not sure you can calculate the month of start date from inside the formula, for a range of dates. Perhaps someone else can describe that.
dm

Hey Dale,
Thanks for this! I tried your formula above but now am getting #DIVIDE BY ZERO as a response.. Any suggestions?

Notice how your Q1  Q5 values are all on the left side of the columns? That means they're being treated as text. So you have to convert them into numeric values before you can perform math functions on them. Whatever formula you are using to get those values, wrap it in the VALUE function to convert the result to a numeric value:
=VALUE(<put your formula here>)
Regards,
Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Jeff, you did it! Thank you so, so much. I appreciate your help!!!!!
Help Article Resources
Categories
Check out the Formula Handbook template!