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
Best Answer

It will basically replace the error with a blank.
Answers

Wrap the formula that is throwing the error in an IFERROR function to output a blank.
=IFERROR(original_formula, "")

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

You would wrap it the same way I have in my previous comment.
=IFERROR(original_formula, "")
=IFERROR(NETWORKDAY([Discovery Date]1, [Date Resolved]1), "")

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

It will basically replace the error with a blank.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.4K Get Help
 364 Global Discussions
 200 Industry Talk
 430 Announcements
 4.4K Ideas & Feature Requests
 137 Brandfolder
 129 Just for fun
 128 Community Job Board
 446 Show & Tell
 28 Member Spotlight
 1 SmartStories
 284 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!