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

1

Instead of

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

use

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

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

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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