PC Review


Reply
Thread Tools Rate Thread

Re: Checking sums won't add up

 
 
Don Guillett
Guest
Posts: n/a
 
      16th Mar 2012
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.
 
Reply With Quote
 
 
 
 
joeu2004
Guest
Posts: n/a
 
      16th Mar 2012
"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.


 
Reply With Quote
 
 
 
 
joeu2004
Guest
Posts: n/a
 
      16th Mar 2012
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")

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Re: Checking sums won't add up joeu2004 Microsoft Excel Discussion 0 16th Mar 2012 11:14 AM
Report Sums of Sums SNCONC Microsoft Access Reports 1 31st Mar 2009 01:22 PM
How to add previous sums in a column to current sums in a column? =?Utf-8?B?VEQ=?= Microsoft Excel Worksheet Functions 1 30th Sep 2006 02:55 PM
App for checking MD5 sums =?Utf-8?B?S2VubnkgTG91ZGVu?= Windows Vista General Discussion 1 9th Jun 2006 04:12 AM
Excell will not add multiple sums together, what do I have turned. =?Utf-8?B?S2VubnkncyBLZXkgV2VzdA==?= Microsoft Excel Misc 1 30th Jan 2005 02:04 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 02:23 PM.