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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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)EditPasteSpecial
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)EditPasteSpecial
Add
Values
5)Click [OK]

I hope that helps.

Regards,
Ron