 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Excel Errors / July 2005excel 2003 giving wrong answers to 'sum' and additions
I am now using Excel 2003, and have imported a spreadssheet from excel 2000. the problem now, is that the 'sum' function and function are giving wrong answers. How can I correct this, as it is negating the entire spreadsheet? Could you be more specific, perhaps an example.  SignatureRegards; Rob ------------------------------------------------------------------------ > I am now using Excel 2003, and have imported a spreadssheet from excel 2000. > the problem now, is that the 'sum' function and function are giving wrong > answers. > > How can I correct this, as it is negating the entire spreadsheet? Hi Rob, Ok, My spreadsheet is a std format cashflow fore cast, with income at the top,and expenditure at the bottom of the spreadsheet. The problem I am having, is that the subtotals for income (Income + previous balance) a simple calculation is returning an incorrect answer, and it can be as much as £2.40 out. Thefigures a=part of the spreadsheet is all formated as currency. (two decimal places, no prefix such as £ or $) Also, I am having similar problems further down the spreadsheet, when ependiture subtracted from income to reveal aa working balance...... > Could you be more specific, perhaps an example. > [quoted text clipped - 3 lines] > > > > How can I correct this, as it is negating the entire spreadsheet? Is calculation set to manual? If you remove formatting from a suspicious column are there any numbers that are mysteriously left aligned? That is being seen as text and therefore not being used in any sum? Is the sheet linked to a workbook. If so, have you checked the links via Edit>Links... I know of no specific problem with Excel 2003 that would suddenly exhibit this behaviour SignatureHTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS > Hi Rob, > [quoted text clipped - 21 lines] >> > >> > How can I correct this, as it is negating the entire spreadsheet? = Thanks for getting back to me Nick, 1.No the calculation is set to automatic. 2. Tried removing and manually checking formats, all are formatted to 'nuber' 2 decimal places, show red for negative values. 3. Sheet not linked to remote workbook, only to other sheets within the same workbook. example: 574.88 + 74.56 = 649.54: but is showing 649.33...- obviously incorrect. it also shows wrong result in autocalc i status bar....and its driving me nuts!!!! > Is calculation set to manual? > [quoted text clipped - 33 lines] > >> > > >> > How can I correct this, as it is negating the entire spreadsheet? "example: 574.88 + 74.56 = 649.54: but is showing 649.33..." How are the values derived? i.e. is the incorrect sum the result of a "Sum" function: how is the 574.88 & 74.56 arrived at? SignatureRegards; Rob ------------------------------------------------------------------------ > = Thanks for getting back to me Nick, > [quoted text clipped - 47 lines] > > >> > > > >> > How can I correct this, as it is negating the entire spreadsheet? The values are derived using the 'sum' function, but I have tried re-entering it as '= D3 +D4' , but again, this give the incorrect result. Regards Dave > "example: 574.88 + 74.56 = 649.54: but is showing 649.33..." > [quoted text clipped - 54 lines] > > > >> > > > > >> > How can I correct this, as it is negating the entire spreadsheet? Result is derived using the 'sum' function, however, I have also tried manually entering the following formula: '= D3 + D4' but this also gives the wrong answer. Regards Dave > "example: 574.88 + 74.56 = 649.54: but is showing 649.33..." > [quoted text clipped - 54 lines] > > > >> > > > > >> > How can I correct this, as it is negating the entire spreadsheet? Dave I think the point that was being asked was how the figures in D3 and D4 were being derived from on the basis that there can be a compound effect. If it is that frustrating, I will take a look at the workbook if you want to mail it to me. Take out the obvious to mail me. SignatureHTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS > Result is derived using the 'sum' function, however, I have also tried > manually entering the following formula: '= D3 + D4' but this also gives [quoted text clipped - 75 lines] >> > > >> > How can I correct this, as it is negating the entire >> > > >> > spreadsheet? Hi Nick, I did mail you a copy of the offending spreadsheet, but I don't know if you received it, as I havent heard anything?? My best Dave > Dave > [quoted text clipped - 83 lines] > >> > > >> > How can I correct this, as it is negating the entire > >> > > >> > spreadsheet? Sorry, been away a few days, I'll take a look SignatureHTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS > Hi Nick, > [quoted text clipped - 106 lines] >> >> > > >> > How can I correct this, as it is negating the entire >> >> > > >> > spreadsheet? Dave I'm seeing nothing but the age old rounding problem Using your cells as an example you have a slight error in what's in them (At least as I'm seeing it) D3 = 574.88 D4 = 74.46 D5 = 649.33 (Derived as sum of the above two) Of course the sum looks wrong, but in fact if you expand the number of decimal places the data in the two cells is actually 574.875 and 74.455, which when added together gives the correct answer. When formatting in Excel it only formats the display, not the underlying data which is always to a maximum of 15 digits. To overcome this you can take two courses of action 1) Use the ROUND function to truncate the data to a true number of decimals 2) Set via Tools>Options...>General and check 'Precision as displayed' which will also truncate the date to what is displayed. Other than this I see no fundamental issues with the spreadsheet, calculations, etc. Clue: The mysterious 0.005 is coming through from your Timesheet sheet. This is where you should be using ROUND SignatureHTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS > Sorry, been away a few days, I'll take a look > [quoted text clipped - 110 lines] >>> >> > > >> > How can I correct this, as it is negating the entire >>> >> > > >> > spreadsheet? Sorry Tools>Options...>CALCULATION> check 'Precision as displayed' SignatureHTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS > Dave > [quoted text clipped - 144 lines] >>>> >> > > >> > How can I correct this, as it is negating the entire >>>> >> > > >> > spreadsheet? Thanks for your help Nick, much appreciated. I have done as you said, and the whole sheet now akes sense, except for column 'Q' which no matter which way I look at it differs from the same account in Microsoft Money, by £2.00, and yet both calcs checkout with a calculator......and I cannot find out why, I must have my stupid head on today!!! > Sorry > [quoted text clipped - 148 lines] > >>>> >> > > >> > How can I correct this, as it is negating the entire > >>>> >> > > >> > spreadsheet? Dave Sorry, all checks out for me. If the number you are 'variant' by is £2.00 then all I can think is that the £2.00 in cell Q2 may not be in Money. Certainly the column in Excel checks out exactly SignatureHTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS > Thanks for your help Nick, much appreciated. > [quoted text clipped - 171 lines] >> >>>> >> > > >> > How can I correct this, as it is negating the entire >> >>>> >> > > >> > spreadsheet? OK Nick, Thanks for your help > Dave > [quoted text clipped - 180 lines] > >> >>>> >> > > >> > How can I correct this, as it is negating the entire > >> >>>> >> > > >> > spreadsheet? |
Reply to this Message |
 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.