Adding total number of hours worked and taking the average

[LORACARYN]

It's easy adding 2 or 3 cells but adding up several cells becomes more complicated.

9:03	9:03
8:51	8:51
9:15	9:15
8:12	8:12
4:47	4:47
7:28	7:28
9:06	9:06
8:53	8:53
9:06	9:06
9:23	9:23
7:51	7:51
9:02	9:02
8:46	8:46
8:30	8:30
9:48	9:48
10:04	10:04
9:03	9:03
7:49	7:49
8:01	8:01
18:58	00:00
08:34	#DIV/0! <tbody> </tbody>

<colgroup><col span="2"></colgroup><tbody>
</tbody>

The first column was correctly added where the TOTAL = 18:58 and AVERAGE = 08:34 However, with this first column, I used the long formula = a1+a2+a3+a4+a5+a6... and then to take the average =a36/19 BUT THIS IS THE LONG WAY... which isn't practical in the office.

Looking at the 2nd column, I tried the shortcut =sum(a1:a35) The result was 00:00 What am I doing wrong?
I even custom formatted it to hh:mm
I tried using =average(a1:a35) Nothing's coming out or I get a #DIV/0! What am I doing wrong?

 

[Alex0013]

Hello and welcome to the forums,

When I replicate what you say above, I actually do get 18:58 and 8:34 using =sum() and =average().

Unless you have more rows in your spreadsheet then you pasted above, you only have 29 rows showing, and thus it should be =sum(B1:B29) and =average(B1:B29).

If you do include the cells themselves, you will probably get a circular reference error, and when I did this, I did get 0 for both the sum and the average. Check to make sure you are summing and averaging the correct ranges.

Regards,
Alex

 

[My Aswer Is This]

Same here. The Sum worked for me. Using Excel 2013.

 

[mfexcel]

Why the total is 18:58?? Shouldn't it be 162:58??

The first column was correctly added where the TOTAL = 18:58 and AVERAGE = 08:34 However, with this first column, I used the long formula = a1+a2+a3+a4+a5+a6... and then to take the average =a36/19 BUT THIS IS THE LONG WAY... which isn't practical in the office.

 

[LORACARYN]

Why the total is 18:58?? Shouldn't it be 162:58??

That's right! There's something wrong with the total hahaha How do you fix that?

 

[mfexcel]

Use the custom format [h]:mm

That's right! There's something wrong with the total hahaha How do you fix that?