Checking sums won't add up

[BRUCE STUBBLEFIELD]

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 have 3 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!

 

[Ron Rosenfeld]

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 have 3 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!

35 min 29 sec plus 24 min 36 sec does equal 1 hr 5 sec as your math shows. Without having the data available, and the formulas used to create them, one can only speculate as to the problem.

I would suggest posting your worksheet to one of the sharing sites (I use Skydrive, but there are others) so we can look at the data and formulas you are using.

 

[joeu2004]

Using Excel 2004 for Mac on OS 10.5.8.

I know nothing about the Mac and Excel 2004 for the Mac.

The following is written from the perspective of Excel and Windows on
non-Mac computers. I suspect that similar principles apply to the Mac.

I have a column of 61 entries, all formatted HH:MM:SS
(I checked the formatting). I have 3 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. [....]
Some formatting or other obscure thing?

Formatting should make no difference, unless Excel 2004 for the Mac has the
"Precision as displayed" calculation option (PAD), and you enabled PAD. Did
you?

In any case, it is probably an issue with rounded v. unrounded sums, which I
will demonstrate below.

In order for us to know "for sure", we would need to see the Excel file.
Normally, we suggest that you upload an example Excel file to a file-sharing
website and post the URL (aka link; http://...) of the "shared" file in a
response here. The following is a list of some free file-sharing websites;
or use your own.

Box.Net: http://www.box.net/files
Windows Live Skydrive: http://skydrive.live.com
MediaFire: http://www.mediafire.com
FileFactory: http://www.filefactory.com
FileSavr: http://www.filesavr.com
FileDropper: http://www.filedropper.com
RapidShare: http://www.rapidshare.com

However, the fact that you are using Excel 2004 for the Mac will present
problems for the majority us, I believe. If you can save the Excel file in
a form that is compatible with non-Mac Excel versions, great! Otherwise, a
"csv" file would be useless for our purposes, I believe.

So you might need to work through the following on your own.

I suspect that the sums for #2 and #3 are rounded to the second, whereas the
sum for #1 is "exact" (within the precision of the way that Excel represents
time internally).

To demonstrate, I created an Excel file where column A contains a list of 31
times ("A" songs), and column B contains a list of 30 times ("B" songs).

The key thing to note is: the times are accurate to 1/10-second, even
though they might be formatted as hh:mm:ss. (Note: I do __not__ set PAD.)

The sums for #2 and #3 are created with formulas of the form
=SUMPRODUCT(--TEXT(A2:A32,"hh:mm:ss")). That computes a rounded sum. The
results are 00:35:29 and 00:24:36 respectively. And if we sum those totals
(#4), the result is 01:00:05.

However the sum for #1 is created with the formula =SUM(A2:B32). In my
case, the result is 0:59:34.600. In your case, the sum might be between
0:59:33.500 and 0:59:34.499. So my result differs by only 0.200 sec. (I
just got tired fiddling with the numbers.)

For this example, the values in column A are: 21 values 00:01:09.500, 9
values of 00:01:05.500, and 1 value of 00:01:04.600.

The values in column B are: 6 values of 00:00:45.500, 18 values of
00:00:46.500, and 6 values of 00:00:58.500.

Obviously, these are contrived values. Your data are probably very
different.

But my example demonstrates the circumstances that might lead to the results
that you see. That is: it is possible for the rounded and unround sums to
differ by so much.

The remedy depends on what you want for a result. Only you can answer that.

And the implementation of a solution will depend on details that you have
not revealed to us; for example the formulas used, the state of Excel
options (notably PAD), etc.

 

[Don Guillett]

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]

Many thanks for the generous responses!
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.

At any rate, the formatting of the time window doesn't offer any way to set rounding, or to enter a value with smaller increments than hh:mm:ss. Also, the values I entered were already set at 1 second as the smallest increment.

Thanks
Bruce S

 

[Ron Rosenfeld]

Many thanks for the generous responses!
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.

At any rate, the formatting of the time window doesn't offer any way to set rounding, or to enter a value with smaller increments than hh:mm:ss. Also, the values I entered were already set at 1 second as the smallest increment.

Thanks
Bruce S

The problem is that in Total Songs, you have E84 added twice

 

[Ron Rosenfeld]

Many thanks for the generous responses!
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.

At any rate, the formatting of the time window doesn't offer any way to set rounding, or to enter a value with smaller increments than hh:mm:ss. Also, the values I entered were already set at 1 second as the smallest increment.

Thanks
Bruce S

Ah, not only is E84 repeated, but also E40

 

[joeu2004]

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.

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]

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