Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / October 2006

Tip: Looking for answers? Try searching our database.

SUM error

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
DavidB - 12 Oct 2006 00:43 GMT
Can anyone tell me why this: =SUM(AI13+AW13+BI13+BW13+CJ13/5)) returns 34440%
in the destination cell when AI is 89, AW is 90, BI is 78, BW is 71 and CJ is
82??? I don't get it.

Cheers
David
Gary Smith - 12 Oct 2006 01:04 GMT
Your formula has an extra closing parenthesis which should produce an
error message, but note that

=SUM(89+90+78+71+82/5) has the value 344.4, while

=SUM(89+90+78+71+82)/5 has the value 82.

I suspect the latter is what you want.

> Can anyone tell me why this: =SUM(AI13+AW13+BI13+BW13+CJ13/5)) returns 34440%
> in the destination cell when AI is 89, AW is 90, BI is 78, BW is 71 and CJ is
> 82??? I don't get it.

> Cheers
> David

Signature

Gary L. Smith
Columbus, Ohio

MarkN - 12 Oct 2006 01:08 GMT
Hi David,

Your problems are possibly these.

First, the resulting display of 34440% is a formatting issue. To go back to
displaying a regular number you need to click on the Format menu, select
Cells and make sure the Number tab is selected. Click on General at the top
of the list on the left and click OK.

Second the calculation. A mathematical order of operations governs the way
in which your formula is evaluated:
=AI13+AW13+BI13+BW13+CJ13/5 will mean that CJ13/5 is calculated first
followed by the addition of the other cell references, whereas:
=(AI13+AW13+BI13+BW13+CJ13)/5 will ensure that all addition is completed
prior to the total being divided by 5.

Signature

Hope this helps,
MarkN

> Can anyone tell me why this: =SUM(AI13+AW13+BI13+BW13+CJ13/5)) returns 34440%
> in the destination cell when AI is 89, AW is 90, BI is 78, BW is 71 and CJ is
> 82??? I don't get it.
>
> Cheers
> David
Roger Govier - 12 Oct 2006 06:28 GMT
Hi David

Is there any reason why you choose this method rather than the
=AVERAGE(AI13,AW13,BI13,BW13,CJ13)
as I posted yesterday?

As other have pointed out, your calculation is only dividing the figure
in CJ13 by 5, then adding that to the values in the other cells. Just
the /5 outside of the SUM() formula.

Signature

Regards

Roger Govier

> Can anyone tell me why this: =SUM(AI13+AW13+BI13+BW13+CJ13/5)) returns
> 34440%
[quoted text clipped - 4 lines]
> Cheers
> David
DavidB - 12 Oct 2006 09:49 GMT
Roger, there is absolutely no reason why I did that...but don't worry I
amended it and it now read just as you suggested. I think what happened is
that the destination cell L13 was displaying things like ####### and #Value
and 32000% so I tried to modify the formula rather than just ask your advice,
I have a thing about people thinking I'm a complete idiot so my thanks once
again...by the way what day and date is it over in Wales?

Cheers
David

> Hi David
>
[quoted text clipped - 14 lines]
> > Cheers
> > David

Rate this thread:






 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.