HELP! Cell Formatting Causing Inaccurate Results in Formulas

[Randy Larson]

Changing a text field into a number field. The formula doesn't recognize
the data in the cell as a number (even though the format was changed).
And, the formula then displays incorrect results. If I "edit" each cell (the
equivalent in PC Excel is <F2, Enter>) then the formula starts to work.

In the PC version, the cells that have this cell formating issue have a little
green triangle in the upper left hand corner explaining that the cell has a
number formatted as text or is preceded by an apostrophe... You can select
all the cells in the formula range and fix all cells with a few clicks.

In the MAC version of Excel, there is no green triangle in the upper left
corner of the cells indicating this issue. The way to fix it is to "edit" each
cell and click <enter>... You don't change the data, just <ctrl,u,enter> on
each and every cell... and you can watch the formula cell change with each
cell.... If you are dealing with thousands of cells, this really stinks.

Any idea how to fix this or what's going on??

 

[Randy Larson]

Okay - With a little help, I figured it out... the only way to correct the
formulas is to >>cut>>clear all>>paste all the cells that have the
formatting problem...

The trouble is that there are no warnings that your formula might be
incorrectly displaying the results... in the PC vesion, the little green triangle
clues you into the potential problem, with the MAC... there really is no
warning... So, if you import large stores of data from other applications...
BEWARE and run some tests on the data....

randy

 

[Jim Gordon MVP]

Hi

The little triangles are a new feature in recent Windows versions of Excel.
Within a few weeks there will be a new Mac version of Excel. When it comes
out we'll find out whether or not this feature made it to the Mac side of
the street.

I can't figure out what you mean by "The formula doesn't recognize
the data in the cell as a number (even though the format was changed)" What
formula are you using?

-Jim Gordon
Mac MVP

All responses should be made to this newsgroup within the same thread.
Thanks.

About Microsoft MVPs:
http://www.mvps.org/

Before posting a "new" topic please be sure to search Google Groups to see
if your question has already been answered.


----------

 

[Bob Greenblatt]

Changing a text field into a number field. The formula doesn't recognize
the data in the cell as a number (even though the format was changed).
And, the formula then displays incorrect results. If I "edit" each cell (the
equivalent in PC Excel is <F2, Enter>) then the formula starts to work.

In the PC version, the cells that have this cell formating issue have a little
green triangle in the upper left hand corner explaining that the cell has a
number formatted as text or is preceded by an apostrophe... You can select
all the cells in the formula range and fix all cells with a few clicks.

In the MAC version of Excel, there is no green triangle in the upper left
corner of the cells indicating this issue. The way to fix it is to "edit"
each
cell and click <enter>... You don't change the data, just <ctrl,u,enter> on
each and every cell... and you can watch the formula cell change with each
cell.... If you are dealing with thousands of cells, this really stinks.

Any idea how to fix this or what's going on??

Another way to solve this problem, or to make sure that you do not have the
problem is to force all the values to numbers. This can be done pretty
easily by:

1. enter the number 1 in any empty cell.
2. copy that cell
3. Select the range containing the suspected values
4. Go to Edit-Paste Special and select multiply.

This will multiply all the values by one forcing a numeric result.