excel 2003 giving wrong answers to 'sum' and additions

[datago]

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?

 

[RWN]

Could you be more specific, perhaps an example.

 

[datago]

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......

 

[Nick Hodge]

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

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[datago]

= 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!!!!

 

[RWN]

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

 

[datago]

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

 

[datago]

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

 

[Nick Hodge]

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.

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[datago]

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

 

[Nick Hodge]

Sorry, been away a few days, I'll take a look

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Nick Hodge]

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

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Nick Hodge]

Sorry

Tools>Options...>CALCULATION> check 'Precision as displayed'

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[datago]

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

 

[Nick Hodge]

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

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[datago]

OK Nick,

Thanks for your help

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

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)