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.

"BRUCE STUBBLEFIELD" <(E-Mail Removed)> 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" <(E-Mail Removed)> 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.

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")