Re: Checking sums won't add up

Discussion in 'Microsoft Excel Discussion' started by Don Guillett, Mar 16, 2012.

  1. Don Guillett

    Don Guillett Guest

    On Thursday, March 15, 2012 9:19:16 PM UTC-5, BRUCE STUBBLEFIELD wrote:
    > Hello,
    > Using Excel 2004 for Mac on OS 10.5.8.
    > I'm making a cue sheet which adds up timings of songs. I have a column of 61 entries, all formatted HH:MM:SS (I checked the formatting). I have3 totals for the column.
    >
    > 1: sum of all entries, =0:59:34
    >
    > 2: sum of "A" songs, =0:35:29
    > 3: sum of "B" songs. =0:24:36
    >
    > 4: sum of "A" total + "B" total =1:00:05.
    > This is a check sum field that should match #1 above, but doesn't.
    >
    > I have checked many times and can't find the problem. If I'm sure that all of the 4 totals above refer to the correct cels, that there are no overlaps and no omissions, is there any other possible explanation why they don't match? Some formatting or other obscure thing?
    >
    > thanks!


    Try format of [h:mm] for the sum cell.
     
    Don Guillett, Mar 16, 2012
    #1
    1. Advertisements

  2. Don Guillett

    joeu2004 Guest

    Re: Checking sums won't add up 2

    "BRUCE STUBBLEFIELD" <> wrote:
    > I posted the file at Box.com:
    > http://www.box.com/s/248be850c8b98507cfa9
    > Unfortunately, my Excel didn't offer a Windows conversion,
    > but it did say that my version of Excel makes workbooks
    > that are read in both.


    Yes, my mistake. Sleep-deprived! I realized my mistake when I awoke this
    morning. I'm glad you post the Excel away despite my misdirection.


    "BRUCE STUBBLEFIELD" <> wrote:
    > Also, the values I entered were already set at 1 second
    > as the smallest increment.


    The purpose of a check-sum is to ensure that you did not make mistakes, be
    it by omission or by duplication. When things don't add up, you need to
    look very hard for "dumb" mistakes.

    In this case, the problem is quite simple and quite "obvious": you have E84
    and E40 twice in the sum in the addition formula in E92. Change:

    =E88+E87+E85+E78+E75+E67+E66+E65+E55+E50+E47+E45+E84+E84+E40+E40+E20+E38+E35+E33+E29+E54+E39

    to:

    =E88+E87+E85+E78+E75+E67+E66+E65+E55+E50+E47+E45+E84+E40+E20+E38+E35+E33+E29+E54+E39

    and then E90, =SUM(E19:E88), matches F90, =E91+E92.

    Much ado about nothing.
     
    joeu2004, Mar 16, 2012
    #2
    1. Advertisements

  3. Don Guillett

    joeu2004 Guest

    Re: Checking sums won't add up 2

    PS.... I wrote:
    > In this case, the problem is quite simple and quite "obvious": you have
    > E84 and E40 twice in the sum in the addition formula in E92. Change:
    > =E88+E87+E85+E78+E75+E67+E66+E65+E55+E50+E47+E45+E84+E84+E40+E40+E20+E38+E35+E33+E29+E54+E39
    > to:
    > =E88+E87+E85+E78+E75+E67+E66+E65+E55+E50+E47+E45+E84+E40+E20+E38+E35+E33+E29+E54+E39


    Do yourself a favor and change the design to simplify the formulas in E90
    and E91 and make them more reliable.

    In a parallel column (suppose you insert new column F), enter the letter "A"
    or "B" for each song. Then the formulas in E91 and E92 become:

    E91: =SUMIF(F19:F88,"A",E19:E88)
    E92: =SUMIF(F19:F88,"B",E19:E88)

    And as a check, you might verify the following two results are the same:

    F91: =ROWS(F19:F88)
    F92: =COUNTIF(F19:F88,"A")+COUNTIF(F19:F88,"B")
     
    joeu2004, Mar 16, 2012
    #3
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Guy Laurent

    Weekly spending sums

    Guy Laurent, Dec 3, 2003, in forum: Microsoft Excel Discussion
    Replies:
    1
    Views:
    248
    Dave Peterson
    Dec 4, 2003
  2. FJDx

    Dates, lookups, and sums

    FJDx, Feb 2, 2004, in forum: Microsoft Excel Discussion
    Replies:
    2
    Views:
    262
  3. Mark

    working out sums over a number of sheets

    Mark, Feb 26, 2004, in forum: Microsoft Excel Discussion
    Replies:
    3
    Views:
    215
    Norman Harker
    Feb 27, 2004
  4. JR

    Add Space To Cell Sums

    JR, Dec 12, 2005, in forum: Microsoft Excel Discussion
    Replies:
    2
    Views:
    159
  5. joeu2004

    Re: Checking sums won't add up

    joeu2004, Mar 16, 2012, in forum: Microsoft Excel Discussion
    Replies:
    0
    Views:
    434
    joeu2004
    Mar 16, 2012
Loading...

Share This Page