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

Find more posts tagged with

Accepted answers

All comments

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

Amy Shank

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?

Amy Shank

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.

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.

Amy Shank

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

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

Amy Shank

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

Genevieve P.

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.