AVG TURN TIME?
Hello! I am using the NETWORKDAYS function to calculate the number of days in the Turn Time column. I can't figure out what formula to use to find the average, excluding the cells with #Invalid Data Type. The hover pop-up at the bottom shows the correct answer (6.15) but I don't know how to get there myself!
Best Answer
-
Hi @Matt Corder
I agree with @BullandKhmer!
Formulas don't like referencing columns that contain a formula error. It will see the error and then produce an error itself, even though you want it to skip those cells.
The easiest way to fix this would be to wrap an IFERROR function around whatever formula you have in your Turn Time column, like so:
=IFERROR(formula, "")
So:
=IFERROR(NETWORKDAYS(formula), "")
Then once the entire column is clear of errors, you can Average the column with another formula:
=AVG([Turn Time]:[Turn Time])
Cheers,
Genevieve
Join us for Jumpstart 2025 with Community on 23 January (in two time zones)! 🎉 Register here.
Answers
-
you can replace the #invalid data with a blank cell by...
=IF(ISBLANK[completed first draft],"",[ORIGINAL FORMULA])
That should resolve the issue.
-
Thanks @BullandKhmer , but I am not trying to replace the #invalid data with a blank cell.......I am trying to figure out what formula they used to get the average of the column [Turn Time]. When I click on the column in SmartSheet, the correct average 6.15 automatically pops up at the bottom, so I know it can be done without modifying my column properties.
@Genevieve P. I have been studying your comments in this discussion below, and it sounds very similar to my problem. I would greatly appreciate any words of wisdom you may have!
-
@Matt Corder my point being you cant average a column with #invalid date in it, so if you remove them you can. This is how u remove them.
Well we can average with averageif
=averageif([turn time], <>"# INVALID DATA")
But removing errors is just a better solution.
-
Hi @Matt Corder
I agree with @BullandKhmer!
Formulas don't like referencing columns that contain a formula error. It will see the error and then produce an error itself, even though you want it to skip those cells.
The easiest way to fix this would be to wrap an IFERROR function around whatever formula you have in your Turn Time column, like so:
=IFERROR(formula, "")
So:
=IFERROR(NETWORKDAYS(formula), "")
Then once the entire column is clear of errors, you can Average the column with another formula:
=AVG([Turn Time]:[Turn Time])
Cheers,
Genevieve
Join us for Jumpstart 2025 with Community on 23 January (in two time zones)! 🎉 Register here.
-
Thank you so much @BullandKhmer and @Genevieve P. ! I just needed a bit more context/explanation to be able to connect all the dots. My sheet works perfectly now! I am a newbie to Smartsheet so thank you both for your time and patience.
Matt
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.2K Get Help
- 445 Global Discussions
- 143 Industry Talk
- 476 Announcements
- 5K Ideas & Feature Requests
- 84 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 301 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!