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

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    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.


    Jeff Reisman

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!