#Invalid Data Type

Bill Iuliano

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

Genevieve P.

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

Genevieve P.

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

Bill Iuliano

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....

Genevieve P.

Hi Bill,

Good find - I definitely would have missed something that small on my own sheet. 🙂

I'm glad you sorted it out!

PeggyLang