Excel Function - Formula Issue

[txgeekgirl1]

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?

 

[SkipVought]

Hi,

Why would you even want to HARD CODE values into a formula like that?

Furthermore, Excel thinks that 11:12, is row 11 thru row 12, and it goes downhill from there!!!

As a practice it is MUCH preferable to use references and hard coded values.

FYI, I would not SUM below the range. What happens if you need to add a value??? Rather, put your SUM in row 1, and your data following. If you will be adding data, make the range for the SUM like this...
[tt]
=SUM(A2:A65536)
[/tt]
FYI2: faq68-5827



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[GlennUK]

=SUM(11:12, 5:32, 106:32, 55:03)/4 and format for [h]:mm

...

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

yeah, sure, like this:

=SUM("11:11","5:45","7:02","132:05")/4

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[GlennUK]

By the way, I must also say that I agree with Skip's points

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[txgeekgirl1]

Dept Head asked the question and I told her to put her values in a cell instead of trying it on the fly. These are great responses that will show her to rethink her choice.