0

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.

enter image description here

Improve this question

2 Answers 2

Reset to default
1

Instead of

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

use

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

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

Improve this answer
1

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<>""))))

Improve this answer
2
  • Thanks bud, the updated answer didnt work but the initial did. Would also accept this one if I could
    – Trevor Wood
    Nov 8, 2019 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, 2019 at 0:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.