sum formula not working, producing incorrect answers Excel 2003

I am a pretty competent user of Excel, but on some occassions, a simple
formula like SUM will not work. Whether doing it using the Sum (Sigma) icon
or creating the formula in the formula bar (ex. =A12+A13) it is not working.
I have tried clearing and deleting the cells and recreating them, it doesn't
matter. If I redo the formula with the actual number values in each cell it
will work but it is as if it isn't reading the values in the cells properly.
I have tried looking at the cell properties and can not find anything
obvious. Please help. Thank you. - CG

It might be a rounding error problem. If you have 1.4 in both A12 and
A13 but these are formatted as number with 0 decimal places, they will
both show as 1, but the answer to the formula will show as 3.

Pete

Pete,

By the way one other thing I should have mentioned is that I did check and
verify the the Tools-Options-Calculations is set to automatic as I noticed
that advice on several other related posts.

CG

"Pete" wrote:

> It might be a rounding error problem. If you have 1.4 in both A12 and
> A13 but these are formatted as number with 0 decimal places, they will
> both show as 1, but the answer to the formula will show as 3.
>
> Pete
>
>

Pete,

That is not the case, I have 6535 in one cell, trying to add it to (3700)
and it is coming up with zero...I have also tried moving other cells and
doing the same thing and have come up with other incorrect answers. I have
also tried putting the following:
A12+A13
A12-A13
A12+(A13)
and A12-(-A13)
None of these have produced the correct answer of 2835 which I could easily
do by hand, but I am trying to do a 5 year business p&l build out and that
will take forever and lead to issues if I can not get a simple formula like
SUM to work in this worksheet.

Also in the short time it took you to respond (which thank you so much for
the incredibly quick response) I tried duing the Error Check tool and now
every formula in the spread sheet is showing the formula and not the value
and I have looked around and can not figure out how to change it back to
normal. If you could advise on that as well it would be appreciated.

CG

"Pete" wrote:

> It might be a rounding error problem. If you have 1.4 in both A12 and
> A13 but these are formatted as number with 0 decimal places, they will
> both show as 1, but the answer to the formula will show as 3.
>
> Pete
>
>

Hi,
I was able to recreate your problem by going to Tools/Options/Transition and
putting a check mark in "Transition Formula Evaluation".

Not sure what it does tho.

HTH
JG

"crzyg8r" wrote:

> Pete,
>
> By the way one other thing I should have mentioned is that I did check and
> verify the the Tools-Options-Calculations is set to automatic as I noticed
> that advice on several other related posts.
>
> CG
>
> "Pete" wrote:
>
> > It might be a rounding error problem. If you have 1.4 in both A12 and
> > A13 but these are formatted as number with 0 decimal places, they will
> > both show as 1, but the answer to the formula will show as 3.
> >
> > Pete
> >
> >

Hi!

Use the key combo of CTRL ` to toggle between formula view and normal view
or goto Tools>Options>View>Formulas.

Your problem might be that some of your values have been formatted as TEXT.

A1 = 6535
A2 = 3700

=SUM(A1:A2)

If that formula returns 0 then the values are TEXT

Try this:

Select an empty cell and make sure that it's formatted as GENERAL

Copy that empty cell
Select the cells that contain your number values
Goto Edit>Paste Special>Add>OK

That will "force" the values to convert to a GENERAL format.

Biff

"crzyg8r" <[email protected]> wrote in message
news:[email protected]...
> Pete,
>
> That is not the case, I have 6535 in one cell, trying to add it to (3700)
> and it is coming up with zero...I have also tried moving other cells and
> doing the same thing and have come up with other incorrect answers. I
> have
> also tried putting the following:
> A12+A13
> A12-A13
> A12+(A13)
> and A12-(-A13)
> None of these have produced the correct answer of 2835 which I could
> easily
> do by hand, but I am trying to do a 5 year business p&l build out and that
> will take forever and lead to issues if I can not get a simple formula
> like
> SUM to work in this worksheet.
>
> Also in the short time it took you to respond (which thank you so much for
> the incredibly quick response) I tried duing the Error Check tool and now
> every formula in the spread sheet is showing the formula and not the value
> and I have looked around and can not figure out how to change it back to
> normal. If you could advise on that as well it would be appreciated.
>
> CG
>
> "Pete" wrote:
>
>> It might be a rounding error problem. If you have 1.4 in both A12 and
>> A13 but these are formatted as number with 0 decimal places, they will
>> both show as 1, but the answer to the formula will show as 3.
>>
>> Pete
>>
>>

I was able to correct my formula by checking for any circular reference. It happened that one of my manual subtotal has circular reference/incorrect formula that's why the other total was not working.