Averageif function not working?

Dan Tanner

Hello.

I am trying to average two columns in my smartsheet. The issue i'm having is this:

A lot of the cells have a "divide by zero" error. This is a project tracker where I can't enter numbers until they are complete.

When I "averageif" for completed projects, I am still getting the "divide by zero" error, even though the IF criteria doesn't have any zeros or "divide by zero" errors.

One more thing - I created a report where I filtered the exact same criteria as the "averageif" and it works just fine…

Hoping this is enough info without having to share screenshots but I can if needed. Please help my brain is apparently still on holidays.

Paul Newcome

If there is an error in even one cell being referenced, that error will pull through even if the error would not be pulled in by the range/criteria filtering.

That particular error can also come from pulling in no rows based on the range/criteria set. So if I am telling it to collect all rows that are greater than zero, but there are no rows greater than zero, then we are dividing by zero.

If you are confident your formula will work once you add the data whether that be because you already tested it or it is already functioning for another data set, and you just want to output a blank until data is entered, you would use an IFERROR statement like so:

=IFERROR(AVERAGEIF(………), "")

If none of the above helps, screenshots would be helpful in further trouble shooting.