AVERAGEIF returning 0

David Acord

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]

Jeff Reisman

@David Acord

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>)

https://help.smartsheet.com/function/value

Dale Murphy

@David Acord

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

David Acord

Hey Dale,

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

Jeff Reisman

@David Acord

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>)

https://help.smartsheet.com/function/value

David Acord

Jeff, you did it! Thank you so, so much. I appreciate your help!!!!!