# Adding total number of hours worked and taking the average

#### LORACARYN

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

9:039:03
8:518:51
9:159:15
8:128:12
4:474:47
7:287:28
9:069:06
8:538:53
9:069:06
9:239:23
7:517:51
9:029:02
8:468:46
8:308:30
9:489:48
10:0410:04
9:039:03
7:497:49
8:018:01
18:5800: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?

### Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

#### Alex0013

##### Board Regular
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

##### Well-known Member
Same here. The Sum worked for me. Using Excel 2013.

#### mfexcel

##### Well-known Member
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

##### New Member
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

##### Well-known Member
Use the custom format [h]:mm

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

Replies
1
Views
328
Replies
9
Views
211
Replies
7
Views
304
Replies
5
Views
269
Replies
1
Views
315

1,191,172
Messages
5,985,086
Members
439,940
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back