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

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, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Answers

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, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
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, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
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.
=AVG(IF(ISNUMBER([Planning notified  QSTO Received]:[Planning notified  QSTO Received]), [Planning notified  QSTO Received]:[Planning notified  QSTO Received]))

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, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
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, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
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, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Help Article Resources
Categories
Check out the Formula Handbook template!