# How to get the average of times while skipping over empty cells?

I'm trying to make a formula that looks over all of the times and returns an average of the time but skips blank cells. I'm really close, just can't get it to skip over blank cells.

Here's what I currently have

`=ARRAYFORMULA(AVERAGE(TIMEVALUE(E4:G4)))`

`=ARRAYFORMULA(AVERAGEIF(TIMEVALUE(E4:G4),"<>"))`This one worked for numbers but not time for some reason. ## 2 Answers

Instead of

``````=ARRAYFORMULA(AVERAGE(TIMEVALUE(E4:G4)))
``````

use

``````=ARRAYFORMULA(AVERAGE(IFERROR(TIMEVALUE(E4:G4),)))
``````

The above because `TIMEVALUE` returns `#VALUE!` for blank cells.

UPDATE

Since it is a single line, there is no reason to use the `ARRAYFORMULA` function because `ARRAYFORMULA` could alter your results.
It will -given the right data- round the results to 2 decimal points.
The most accurate results you get by using:

``=AVERAGE(TIMEVALUE(FILTER(E4:G4,E4:G4<>"")))``

Please have a look at the examples in the Test Sheet I prepared for you.

### Initial answer

You can use the `FILTER` function to omit/skip empty cells.

``=ARRAYFORMULA(AVERAGE(TIMEVALUE(FILTER(E4:G4,E4:G4<>""))))``

• Thanks bud, the updated answer didnt work but the initial did. Would also accept this one if I could – Trevor Wood Nov 8 '19 at 0:26
• "the updated answer didnt work ". Why not? Don't you have a single row? Have you checked the Test Sheet? It works there. – marikamitsos Nov 8 '19 at 0:44