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

slando

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

Paul Newcome

It will basically replace the error with a blank.

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.