Average Of Time Values, Ignore Errors

[mouse88]

I have the following formula:

{=AVERAGE(TIMEVALUE(I4:I9))}

This works fine until one of the values in the range has a #N/A! error. Is there any way I can get it to ignore these errors and only use the time values?

Thanks

Matt

 

[Andrew Poulsom]

This worked for me:

=AVERAGE(IF(ISNA(I4:I9),"",TIMEVALUE(I4:I9)))

Don't forget Ctrl+Shift+Enter.

 

[mouse88]

I get a #VALUE! error with this.

 

[mouse88]

I got it working with this:

=(SUMIF(E6:E8,"<>#N/A"))/COUNTIF(E6:E8,"<>#N/A")

However if all the values are #N/A then it returns a #VALUE! error. I need to return TIMEVALUE("00:00:00") instead of an error.

 

[Matty]

Maybe:

=IFERROR(SUMIF(E6:E8,"<>#N/A")/COUNTIF(E6:E8,"<>#N/A"),"00:00:00")

Matty

 

[Scott Huish]

Why do you need TIMEVALUE?

=AVERAGE(IF(ISNUMBER(I4:I9),I4:I9))

Confirm with CTRL-SHIFT-ENTER rather than just Enter.

 

[mouse88]

Because im averaging time values in the format hh:mm:ss and not numbers.

 

[T. Valko]

Because im averaging time values in the format hh:mm:ss and not numbers.

True time values are numbers. They just look different due to formatting that's applied (sometimes automatically without you having to do it).

12:00 PM = numeric 0.5
8:00 AM = numeric 0.333333333333333

Select a cell that contains a time entry
Change the cell format to General
You'll see the TRUE numeric value of the cell

 

[barry houdini]

TIMEVALUE doesn't work when applied to real times, only text values, so I'm confused how the original formula could work, i.e.

{=AVERAGE(TIMEVALUE(I4:I9))}

but that this would also work

=(SUMIF(E6:E8,"<>#N/A"))/COUNTIF(E6:E8,"<>#N/A")

unless you really are talking about two different ranges.....

 

[mouse88]

Both these formulas work:

=(SUMIF(E6:E8,"<>#N/A"))/COUNTIF(E6:E8,"<>#N/A")

=AVERAGE(IF(ISNUMBER(I4:I9),I4:I9))

But if all values in the range have the #N/A error then it returns a #DIV/0 error.