Number problems

I have a column that is re-formatted as a number. When I try to add the
column, it gives the wrong total.....ie: a cell with a number in it is not
recognized as a number even though it is formated properly. But....and
here's the good part, when I go to that cell and hit F2 then Enter, it
magically becomes a number and adds correctly.

WFT is going on????

Thanks....or am I going insane?

Formatting does not a number make.
Editing (F2 or double clicking) a numeric character will turn it to a digit.
Another way (for many cells): type 1 in an empty cell and Copy; select the
range to be fixed and use Edit|Paste Special->Multiply
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Mark" <[email protected]> wrote in message
news:[email protected]...
>I have a column that is re-formatted as a number. When I try to add the
> column, it gives the wrong total.....ie: a cell with a number in it is not
> recognized as a number even though it is formated properly. But....and
> here's the good part, when I go to that cell and hit F2 then Enter, it
> magically becomes a number and adds correctly.
>
> WFT is going on????
>
> Thanks....or am I going insane?

No need to enter the 1 in a cell - copy an empty cell, then instead of using
Multiply in thepaste special, use Add. Works as well.
Bob Umlas
Excel Mvp
"Bernard Liengme" <[email protected]> wrote in message
news:[email protected]...
> Formatting does not a number make.
> Editing (F2 or double clicking) a numeric character will turn it to a
digit.
> Another way (for many cells): type 1 in an empty cell and Copy; select the
> range to be fixed and use Edit|Paste Special->Multiply
> best wishes
> --
> Bernard V Liengme
> www.stfx.ca/people/bliengme
> remove caps from email
>
> "Mark" <[email protected]> wrote in message
> news:[email protected]...
> >I have a column that is re-formatted as a number. When I try to add the
> > column, it gives the wrong total.....ie: a cell with a number in it is
not
> > recognized as a number even though it is formated properly. But....and
> > here's the good part, when I go to that cell and hit F2 then Enter, it
> > magically becomes a number and adds correctly.
> >
> > WFT is going on????
> >
> > Thanks....or am I going insane?
>
>

Excel quirk (sort of): Formatting a number look-alike from text to a number
doesn't change the original number. The number must be edited in some way
that forces Excel to rethink it's status. Here are two typical approaches:

1)Put number 1 in a blank cell.
2)Copy that cell
3)Select the range that needs...um...nudging.
4)Edit>PasteSpecial
Multiply
Values
5)Click [OK]

OR. . .

1)Put number 0 in a blank cell.
2)Copy that cell
3)Select the range that needs...um...nudging.
4)Edit>PasteSpecial
Add
Values
5)Click [OK]

I hope that helps.

Regards,
Ron