0

I Have a column of cells in excel of format hh:mm (The day is NOT included) like:

19:08
19:08
19:40
20:10

1:45 (Any Times before 12:00 noon are considered NEXT DAY)

one (or more of these) is after midnight.

The Average of these times is = 20:46

But Excel AVERAGE gives 15:58

I would like to calculate this average without adding extra columns or using VBA Just one formula at the end of the column. By the way, I also need to exclude any blank entries in the column for the calculations.

Thanks in Advance

  • You'll need an averageif, I think - what do you want to average? Noon to noon like 11:59am is the latest? – Raystafarian Jul 21 '15 at 17:10
  • correct Raystafarian ... Wouldn't AVERAGEIF just exclude entries less than 12:00? rather than adding 24 hours and including them? – Flashheart Jul 21 '15 at 17:19
  • Well since you want to skew the timeline, it will be trickier, I thought you wanted to exclude some. – Raystafarian Jul 21 '15 at 17:22
4
=AVERAGE(IF(A1:A5>=0.5,A1:A5,1+A1:A5))

Confirm as an array formula with Ctrl+Shift+Enter

To exclude blank cells -

=AVERAGE(IF(NOT(ISBLANK(A1:A5)),IF(A1:A5>=0.5,A1:A5,1+A1:A5),""))
  • Seems to work. Is there a way to exclude blank entries in the column? Or is that asking too much of the formula? – Flashheart Jul 21 '15 at 17:37
  • @Flashheart I edited my answer to account for blanks. – Kyle Jul 21 '15 at 17:57
  • Works great ... thanks so much :) – Flashheart Jul 21 '15 at 18:01

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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