How best to AVG a column of numbers created by formulas, and some cells have #INVALID DATA TYPE

Hello,

I have setup a sheet where I have 2 date columns that represent when I open a Support Ticket (Date Discovered & Date Resolved). I added a third column to show me the NETWORKDAY Formula so it will show me the number of "workdays" between those two columns. Works fine!

Now I have a GRAND TOTAL ROW that I'm trying to capture the totals of some columns, one of them is that I am trying to get the AVERAGE of an entire column called "Average AP Support Cases in days", but in some cells it has the #INVALID DATA TYPE which in turn, is preventing me from calculating an overall AVG of the entire column.

Any suggestions?

Sean

Find more posts tagged with

Accepted answers

Paul Newcome

It will basically replace the error with a blank.

All comments

Paul Newcome

Wrap the formula that is throwing the error in an IFERROR function to output a blank.

=IFERROR(original_formula, "")

slando

Paul,

So the formula I use to calculate the total "working days only: on each row is:

=NETWORKDAY([Discovery Date]1, [Date Resolved]1)

but the Date Resolved column is blank of course till it is actually closed, thus creating the #INVALID DATA TYPE in the cell. How would I add the IFERROR to this formula so that the cell will remain blank until it has both dates to do the calculation?

TY in advance - Sean

Paul Newcome

You would wrap it the same way I have in my previous comment.

=IFERROR(original_formula, "")

=IFERROR(NETWORKDAY([Discovery Date]1, [Date Resolved]1), "")

slando

TY so in between the parenthesis is actually blank to account for a blank cell I would imagine - ok TY

That was the part I wasn't clear on

Paul Newcome

It will basically replace the error with a blank.

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.