Querky Excel Formula Issue.

[txgeekgirl]

OK - so as we know, you can have cells that do this:

A1 = 11:11
A2 = 5:45
A3 = 7:02
A4 = 132:05

In cell A5 you can hit your funky E to sum and then reselect the Summed Cell - (it will be incorrect) - and click Format/Cells/Custom/Type = [h]:mm and Voila! it adds hours and minutes. Makes you sooooo happy.

But then... You have another thought.

You have more numbers not in cells and you want to do something like this:
=SUM(11:12, 5:32, 106:32, 55:03)/4 and format for [h]:mm

You get GARBAGE . Your numbers flip because I think it thinks you want to do division of fractions.

Is it possible to on the fly add time like that or must they reside in cells?

 

[vbaInet]

So you simply want to divide ONLY the hours part of the Summed times?

 

[ConneXionLost]

Hi,

...
You have more numbers not in cells and you want to do something like this:
=SUM(11:12, 5:32, 106:32, 55:03)/4 and format for [h]:mm

You get GARBAGE . Your numbers flip because I think it thinks you want to do division of fractions.
...

Actually, Excel sees the colon ":" and attempts to interpret your time values as cell/range references. For example, Excel interprets 5:32 as everything in rows 5 to 32.

It is possible to enter times in a formula, but you'll need to use the TIME function to do it. For example:

TIME(1,35,10)

Pearson describes it better.

Cheers,