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

SIS · 01/25/22

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!

Jeff Reisman · 01/25/22

https://community.smartsheet.com/discussion/comment/317354#Comment_317354

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.

Jeff Reisman · 01/25/22

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.

SIS · 01/25/22

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.

Jeff Reisman · 01/25/22

https://community.smartsheet.com/discussion/comment/317333#Comment_317333

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

SIS · 01/25/22

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

=AVG(IF(ISNUMBER([Planning notified - QSTO Received]:[Planning notified - QSTO Received]), [Planning notified - QSTO Received]:[Planning notified - QSTO Received]))

Jeff Reisman · 01/25/22

https://community.smartsheet.com/discussion/comment/317354#Comment_317354

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.

SIS · 01/26/22

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

Jaime P · 04/11/23

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

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

Jeff Reisman · 04/11/23

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

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

Jaime P · 04/12/23

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

Jeff Reisman · 04/12/23

https://community.smartsheet.com/discussion/comment/371404#Comment_371404

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:

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

Best Answer

Answers

Help Article Resources

Categories