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
-
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.
-
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.
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
@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.
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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. ;(
-
Hi @Amy Shank
Try simply:
=AVG([LE Total Days]:[LE Total Days])
AVG won't be able to identify extra criteria, you would either need to use AVERAGEIF or AVG(COLLECT.
If you want to try filtering cells out, try:
=AVERAGEIF([LE Total Days]:[LE Total Days], ISNUMBER(@cell), [LE Total Days]:[LE Total Days])
Keep in mind the structure of AVERAGEIF is a bit different than what you may think. You need to first list the criteria column, then the criteria, then finally the column you want to average. In your case, it's the same column... which means it should be listed first and last with the criteria between. This means in your formula above, you want the ISNUMBER to be in the middle, otherwise you'll get an error.
Let me know if this works, now!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!