Excel Help (1 Viewer)

[Mike Wegimont]

I have a series if times (seconds, actually) in column A and need to output of the sum (in minutes:seconds) in column B. Here is an example:

:15 :15
:30 :45
:30 1:15
Blank Blank
:20 1:35

There could be blank cells in any row.

I got the minutes:seconds and the sum figured out but adding the blanks, etc. is beyond me.

Thanks in advance.

Mike

 

[Zak Solo]

Hi

I can probably help but need a bit more information.

I work a lot with dates and not so much with time.

What I would do if I were you is to convert the seconds into a number. So:

If you imagine I have to work out for example the number of years and days between two dates lets say:

todays date and 24/6/2004.

As an excel formula I would do it as follows.

=sum(now() minus cell name 6/24/2004)/365.25.

This would give me the answer in years and multiples of the year. Answer 0.82.

So with Time I would work on a similar basis. You know how many seconds in a minute. So 15 seconds becomes 25 as a multiple of 100. You can then multiply it out again to get the seconds.

It is easier to sum up numbers as mutliples rather that seconds.

Let me know if you need anything else.

 

[Zak Solo]

Also to add the up you would work as follows:

A B
1 1 1
2 2 =sum(B1+A2)
3 3 =sum(B2 the cell above) + A3)
4
5
6
7
8

 

[Hunter P]

First, format both columns to display minutes and seconds. This is under the "Format Cells" function. Select the category "Custom" and the type "mm:ss". This will now display your numbers in the minutes and seconds format.

Unfortunately, when entering numbers you have to enter in the hours too. So when entering in 15 seconds you must enter it as:
0:0:15

Now that your columns are formatted, you can use Zak's formula. I will assume that the data are in column A and the sums are in B. Cell B1 would have the following formula:
=SUM(A1)

Cell B2 would have the following formula:
=SUM(B1+A2)

And you could keep re-entering each formula for cells B3 and on or you could do it the easy way. Click on cell B2 and you will notice a small box on the bottom right corner of the cell. This is called the fill handle. When you move your cursor over it you will notice it changes from a white cross to a black cross. Just click and drag it down and it will copy the formula but modify it for the appropriate adjacent cells. Easy as pie.

 

[Mike Wegimont]

Zak,

I was asked to create a spreadsheet to lay out the time in minutes:seconds as opposed to just a running total of seconds since it takes too much time to figure out what 630 seconds is (10m30s).

Your example is correct but does is take blank cells into account? Also, how can I set this up for the whole column as opposed to doing each individual cell?

Thanks,
Mike

 

[Mike Wegimont]

Hunter,

Thanks. The fill handle is great but if any cell is empty it breaks everything.

Here's what I need: if B2 is =SUM(B1+A2) but A3 is blank and A4 has data, B4 should automatically do =SUM(B2+A4).

I think it's the blanks that make it hard because the number of row/cell blanks will vary.

Mike

 

[Hunter P]

If you drag and drop the formula and cell A3 is blank then the sum displayed in B3 should be the same as B2 (i.e. B2+0=B2). Cell B4 should display the correct result since B3=B2. It would look like this:

------A-----B
1----00:15----00:15
2----00:30----00:45
3----(blank)---00:45
4----00:20----01:05


Maybe you don't want the result to show up in column B3? Did you want it to display as:

-------A---------B
1----00:15-----00:15
2----00:30-----00:45
3----(blank)---(blank)
4----00:20-----01:05

If so then you will have to modify the formula into an "if" formula. You would also have to change how the formula adds the cells. Each cell would probably have to be modified (no fill handle shortcut, sorry.) Here is one way of writing the formula:

For cell B1
=IF(A1="","",SUM(A1))

For cell B2
=IF(A2="","",SUM(A1:A2))

For cell B3
=IF(A3="","",SUM(A1:A3))

etc, etc.

With Excel there are at least three ways of doing the same thing so someone might have a better way of doing it.

 

[Mike Wegimont]

Hunter,

The second example is exactly what I want, I think.


C D
1 Length Time
2 0:15 0:15
3 0:30 0:45
4 0:20 1:05
5
6 0:30 =IF(C5="","",SUM(C2:C6)) Returns a blank

 

[Hunter P]

You should change it to reference C6 as in:
=IF(C6="","",SUM(C2:C6))

 

[Mike Wegimont]

Thank You!

Is there any way to automate it?

Mike

 

[Hunter P]

The fill handle won't work for a formula like that. You're gonna have to modify each one.:frowning:

 

[Denward]

=IF(C6="","",SUM(C$2:C6))

Just put in a dollar sign and Excel will lock in row 2 as the starting point for your formula when you copy it down the column.

 

[Hunter P]

Doh, I forgot about the dollar sign thingy. Thanks.

 

[Mike Wegimont]

Great! Thanks.