#Invalid Data Type
I have a successful formula in a sheet summary that is as follows
=AVG(COLLECT([Prototype Lead Time (PO DATE and Ship Date)]:[Prototype Lead Time (PO DATE and Ship Date)], [Target Ship Date]:[Target Ship Date], IFERROR(YEAR(@cell), 0) = 2020, [Target Ship Date]:[Target Ship Date], IFERROR(MONTH(@cell), 0) = 1, [New/ Add'l/ Revised]:[New/ Add'l/ Revised], "New"))
When I try and change the column it is looking at at get #Invallid Data Type Error.
=AVG(COLLECT([Prototype Lead Time (Approval to Ship Date)]:[Prototype Lead Time (Approval to Ship Date)], [Target Ship Date]:[Target Ship Date], IFERROR(YEAR(@cell), 0) = 2020, [Target Ship Date]:[Target Ship Date], IFERROR(MONTH(@cell), 0) = 1, [New/ Add'l/ Revised]:[New/ Add'l/ Revised], "New"))
I have checked both formulas and spelling and references and I am not sure what the issue is
Please help
Best Answer
-
Hi Bill,
There could be a few things going on; it's hard to tell without seeing the sheet or knowing what the columns are. Can you confirm what type of Sheet Summary field you created (Text/Number?)
Can you also confirm what type of column the [Prototype Lead Time (Approval to Ship Date)] column is, and some examples of its values?
The error #INVALID DATA TYPE is saying that the formula either contains or references an incompatible data type, such as =INT("Hello"). This leads me to believe that there may be something in your [Prototype Lead Time (Approval to Ship Date)] column that is unable to be averaged.
Do you have any text in that column, or are any of the values returned by a formula? It would help if we could see a screen capture of your sheet, but please block out any sensitive data.
Thanks!
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
Hi Bill,
There could be a few things going on; it's hard to tell without seeing the sheet or knowing what the columns are. Can you confirm what type of Sheet Summary field you created (Text/Number?)
Can you also confirm what type of column the [Prototype Lead Time (Approval to Ship Date)] column is, and some examples of its values?
The error #INVALID DATA TYPE is saying that the formula either contains or references an incompatible data type, such as =INT("Hello"). This leads me to believe that there may be something in your [Prototype Lead Time (Approval to Ship Date)] column that is unable to be averaged.
Do you have any text in that column, or are any of the values returned by a formula? It would help if we could see a screen capture of your sheet, but please block out any sensitive data.
Thanks!
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Genevieve
Good afternoon. the formula is in a field of a Sheet Summary. The field in the sheet summary is a Text/ number.
The column it is looking at in the sheet is a Text/ number....
Looking down I see that there was a cell waaaaaaaaaaay at the bottom that had a " ) " in it....thank you for the help. I did not see that earlier as I did not scroll down far enough....
-
Hi Bill,
Good find - I definitely would have missed something that small on my own sheet. 🙂
I'm glad you sorted it out!
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.8K Get Help
- 410 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 142 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!