Invalid Data Types

I believed I've fixed the formula to correct the @cell invalid data type error and instead it returns a blank cell. However, I've used the following formulas to try and get the average from a column in my spreadsheet but I keep getting an invalid data type error in the sheet summary formula..Any suggestions?

=AVG([LE Total Days]:[LE Total Days])

=AVG(COLLECT([LE Total Days]:[LE Total Days]), ISNUMBER,(@cell)))



Answers

  • Leibel S
    Leibel S ✭✭✭✭✭✭

    @Amy Shank

    You would need to clear up the errors ion your sheet, otherwise the sheet summary fields referencing them will return an error.

    Side point, your syntax for the second posted formula is incorrect, see below a corrected version:

    =AVG(COLLECT([LE Total Days]:[LE Total Days], [LE Total Days]:[LE Total Days], ISNUMBER(@cell)))

  • Hi Leibel,


    Thank you so much for the reply. I have corrected the errors in the sheet for one of the columns and it is still rendering the invalid data type error in the summary field. Before I do this for the entire sheet, I want to make sure it will work for one column first. Any other ideas?

  • I confirmed that my column "LE Total Days" does not contain any errors and I used the corrected syntax you suggest above and am still getting an "Invalid Data Type" error in the summary field.

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Amy Shank

    How is the "LE Total Days" column being populated? I can see that the numerical values are appearing on the left side of the cell instead of the right, which means that they're somehow seen as text instead of numerical. Are you using a formula to create the number, and if so, what is it? We can likely use the VALUE function to translate these back into numbers.

  • @Genevieve P we are calculating with a formula in the column.

    =NETWORKDAYS(Departure@row, [Report Submission Date]@row) I tried to add the value function to this formula but it didn't correct the error.

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Amy Shank

    Could you try this?

    =VALUE(NETWORKDAYS(Departure@row, [Report Submission Date]@row))

    And then we should add IFERRORS to all your statements. If there's even one cell with an error it will roll up to your other formulas:

    =IFERROR(VALUE(NETWORKDAYS(Departure@row, [Report Submission Date]@row)), "")

    Let me know if either of this helped!

    Cheers,

    Genevieve

  • I used this formula in the summary:

    =AVG([LE Total Days]:[LE Total Days], [LE Total Days]:[LE Total Days], ISNUMBER(@cell))

    and in the rows I used this formula:

    =IFERROR(VALUE(NETWORKDAYS(Departure@row, [Report Submission Date]@row)), "")

    and I am still getting the invalid data type in the summary. ;(

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!