Excel Help

Discussion in 'After Hours Lounge (Off Topic)' started by Mike Wegimont, Aug 28, 2003.

  1. Mike Wegimont

    Mike Wegimont Stunt Coordinator

    Joined:
    Apr 26, 1999
    Messages:
    130
    Likes Received:
    0
    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
     
  2. Zak Solo

    Zak Solo Agent

    Joined:
    Mar 19, 2001
    Messages:
    36
    Likes Received:
    0
    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.
     
  3. Zak Solo

    Zak Solo Agent

    Joined:
    Mar 19, 2001
    Messages:
    36
    Likes Received:
    0
    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
     
  4. Hunter P

    Hunter P Screenwriter

    Joined:
    Sep 5, 2002
    Messages:
    1,483
    Likes Received:
    0
    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.
     
  5. Mike Wegimont

    Mike Wegimont Stunt Coordinator

    Joined:
    Apr 26, 1999
    Messages:
    130
    Likes Received:
    0
    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
     
  6. Mike Wegimont

    Mike Wegimont Stunt Coordinator

    Joined:
    Apr 26, 1999
    Messages:
    130
    Likes Received:
    0
    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
     
  7. Hunter P

    Hunter P Screenwriter

    Joined:
    Sep 5, 2002
    Messages:
    1,483
    Likes Received:
    0
    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.
     
  8. Mike Wegimont

    Mike Wegimont Stunt Coordinator

    Joined:
    Apr 26, 1999
    Messages:
    130
    Likes Received:
    0
    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
     
  9. Hunter P

    Hunter P Screenwriter

    Joined:
    Sep 5, 2002
    Messages:
    1,483
    Likes Received:
    0
    You should change it to reference C6 as in:
    =IF(C6="","",SUM(C2:C6))
     
  10. Mike Wegimont

    Mike Wegimont Stunt Coordinator

    Joined:
    Apr 26, 1999
    Messages:
    130
    Likes Received:
    0
    Thank You!

    Is there any way to automate it?

    Mike
     
  11. Hunter P

    Hunter P Screenwriter

    Joined:
    Sep 5, 2002
    Messages:
    1,483
    Likes Received:
    0
    The fill handle won't work for a formula like that. You're gonna have to modify each one.[​IMG]
     
  12. Denward

    Denward Supporting Actor

    Joined:
    Feb 26, 2001
    Messages:
    552
    Likes Received:
    0
    =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.
     
  13. Hunter P

    Hunter P Screenwriter

    Joined:
    Sep 5, 2002
    Messages:
    1,483
    Likes Received:
    0
    Doh, I forgot about the dollar sign thingy. Thanks.[​IMG]
     
  14. Mike Wegimont

    Mike Wegimont Stunt Coordinator

    Joined:
    Apr 26, 1999
    Messages:
    130
    Likes Received:
    0
    Great! Thanks.
     

Share This Page