# AVERAGEIF returning 0

Options

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]

• ✭✭✭✭✭✭
Options

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:

Regards,

Jeff Reisman

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• ✭✭✭✭✭✭
Options

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

• Options

Hey Dale,

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

• ✭✭✭✭✭✭
Options

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:

Regards,

Jeff Reisman

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• Options

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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!