# Trying to ignore the error cells and calculate average for the column

✭✭

I've been trying to ignore the cells which have #invalid data type calculate the entire column with numbers. Please see attached.

I've tried iferror as well with no luck!!

Will highly appreciate if someone can help out!

Tags:

• ✭✭✭✭✭✭

It's very difficult to help you because you haven't given me any indication of whether or not the formula I offered worked or not, or if you even tried it, and it seems like you're just firing blindly without providing any explanation of why you are trying this other formula.

Your use of AVG/IF/ISNUMBER above does not fit the syntax or examples of that combination of functions, so I don't see how it could possibly work. The logic you have here is:

If this entire column is a number, then entire column, and give me the average of the entire column. The problem is that ISNUMBER can only be used to evaluate a single cell, not a range, and the positive condition of an IF statement can't be a range either.

I know that the formula I provided will work because I wrote and tested it against the scenario you are facing, and it returned valid values for calculating the average.

Regards,

Jeff Reisman

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

• ✭✭✭✭✭✭

What is the column type of the "Planning Notified - QTSO generation" column and what is the data in it? Is it all either numbers or blanks? How is this column being populated, by formula or by data entry?

#INVALID DATA TYPE indicates that you're trying to perform math on things that aren't numbers.

You may try using a helper column with

=IFERROR(VALUE([Planning Notified - QTSO generation]@row), 0)

and then use your AVERAGEIF against this helper column, with criteria set to average anything greater than 0.

Regards,

Jeff Reisman

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

• ✭✭

Those are the number of days between two dates. I'm trying to track each step of the lifecycle management of a product.

Generating those numbers by netdays formula.

• ✭✭✭✭✭✭

Then this is going to be your best bet:

Create a helper column with

=IFERROR(VALUE([Planning Notified - QTSO generation]@row), 0)

This will clean up the data so that anything that won't compute with your netdays formula will become 0 in the helper column. Then use your AVERAGEIF against this helper column, with criteria set to average only the cells greater than 0. (Or, if you want to include average using 0 values that are correctly in there, use -1 instead in the IFERROR and set AVERAGEIF for anything >=0

Regards,

Jeff Reisman

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

• ✭✭

Thanks Jeff. I'm trying the formula below. It is still giving me an error.

• ✭✭✭✭✭✭

It's very difficult to help you because you haven't given me any indication of whether or not the formula I offered worked or not, or if you even tried it, and it seems like you're just firing blindly without providing any explanation of why you are trying this other formula.

Your use of AVG/IF/ISNUMBER above does not fit the syntax or examples of that combination of functions, so I don't see how it could possibly work. The logic you have here is:

If this entire column is a number, then entire column, and give me the average of the entire column. The problem is that ISNUMBER can only be used to evaluate a single cell, not a range, and the positive condition of an IF statement can't be a range either.

I know that the formula I provided will work because I wrote and tested it against the scenario you are facing, and it returned valid values for calculating the average.

Regards,

Jeff Reisman

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

• ✭✭

Jeff, it did actually work a charm! I was trying to avoid another column as this sheet already got around 80 columns! Thanks for the input. I'm gonna go with the helper column!!

• ✭✭

How would I change this formula to make all of the error cells blank?

=IFERROR(VALUE([column name]@row), 0)

• ✭✭✭✭✭✭

@Jaime P Try this, replacing the 0 with two double quotes:

=IFERROR(VALUE([column name]@row), "")

Regards,

Jeff Reisman

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

• ✭✭

@Jeff Reisman This did the trick, Thank you! 😎 I am not sure how to mark it as accepted

• ✭✭✭✭✭✭

It's all good! This thread isn't "yours," so you don't get to accept answers on it! If you had asked the initial question, you'd see this at the bottom of each person's response:

Regards,

Jeff Reisman

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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!