Calculating average time with a dd/mm/yyyy hh/mm notation

[Maurits]

Hey all,

I'm new here and I have an Excel question which is probably very easy for you. I have row of times in the dd/mm/yyyy hh/mm notation. If I calculate the average by =average(A1;A3;A5) it all goes well but at a (for me random) date the average isn't correct any more. It probably has to do with the dd/mm/yyyy part I think but I can't figure it out.

How can I get the average time (only the hh/mm part) if I have a row of dd/mm/yyyy hh/mm cells.

I hope somebody can help me.

Maurits

 

[barry houdini]

Hello Maurits,

What sort of times will you have - will they be between certain hours? Averaging clock time is problematic because of the circular nature of time, e.g. what result would you expect to get if you averaged 2 minutes past midnight and two minutes before, i.e. 23:58 and 00:02.

If you average those 2 then you get 12 noon, you might expect 12 midnight......

Notwithstanding the above caveats you might be able to use an "array formula" like

=AVERAGE(MOD(A2:A10;1))

but are you looking to average every other cell?

 

[Maurits]

Hello Barry,

Thank you for your reply but it does not work in my case. I am indeed looking for the average of every other cell. The circular nature of time is indeed the problem here, but is there no way in Excel to cover this? All the the times are close to each other (all between 21.15 and 22.15).

 

[barry houdini]

OK, if the times are all in that range it shouldn't be a problem to average them. Assuming you have data in A1:A99 this will average the time only in every other cell, starting at A1

=AVERAGE(IF(MOD(ROW(A1:A99)-ROW(A1);2)=0;IF(ISNUMBER(A1:A99);MOD(A1:A99;1))))

That's an "array formula" that needs to be confirmed with CTRL+SHIFT+ENTER

 

[Maurits]

It works! Thanks a lot. I had to adapt it from row to colum and to Dutch (my Excel version is Dutch) but it works.

 

[Maurits]

Hey all,

I still have one question, the formula works but I would like to understand it to. I have been using it now for some time but I have no clue how it works. If somebody could explain that to me, that would be great.

 

[Maurits]

And there is one more problem. Because some of the times of which I need the average are before and after midnight the average is not "correct". For example, when I have the times 23:50 and 00:10 the average time should be 00:00. Is this possible within the same formula?

 

[Maurits]

Hey guys,

I'm still hoping that somebody can explain the following formula

=AVERAGE(IF(MOD(ROW(A1:A99)-ROW(A1);2)=0;IF(ISNUMBER(A1:A99);MOD(A1:A99;1))))

to me. I want to know how it works but I can't figure it out. I have already solved the problem of average time at midnight btw.

Greetings,

Maurits