AVG TURN TIME?

Matt Corder

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!

Genevieve P.

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

BullandKhmer

you can replace the #invalid data with a blank cell by...

=IF(ISBLANK[completed first draft],"",[ORIGINAL FORMULA])

That should resolve the issue.

Matt Corder

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!

https://community.smartsheet.com/discussion/84568/invalid-data-types

BullandKhmer

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

Genevieve P.

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

Matt Corder

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