Formula Being treated as Text

[BBB]

I have Excel 2003 SP1.

Version 11.6355.6408



I have a cell with the formula:

=COUNTIF(A1,"x")

When I copy this cell to another cell, sometimes the destination just
takes the formula and treats it as text. It seems to copy and calc
sometimes and then not others. I tried a brand new worksheet and have
the same intermittant behavior.

I try going into options and Re-Calcing the sheet, that doesn't work.
The cell just contains the text: =COUNTIF(A1,"x") instead of evaluating
the formula. When it does copy and calc properly, then it seems when I
edit the "x" to something else, it goes back to displaying the text of
the formula.

I have my Excel options set to Tools-->Options--Calculations -
Automatic.

Like I said, even when I force a recalc, the cell just displays the
text.

Is there some other setting in options that might be controlling this.
I can't seem to reproduce the behavior on a different PC running the
same version of Excel, this leads me to believe it's a configuration
issue or something.

Thanks

 

[tobriant]

It sounds like you may have the sheet formatted to show the formula. Try
hitting Ctrl and the ~ button. This will toggle between showing the
formula and the result...

 

[Gord Dibben]

Either you are in Formula View or the cells are formatted as text.

Changing the Format alone won't do the trick.

For the first, hit CTRL + `(back quote above TAB key) to toggle View Formulas
on/off.

For the second, Format the cells to General then F2 and ENTER to see results.

If you have already done a great whack of these formulas, select all and
Edit>Replace

what: =
with: =

Replace All and OK.


Gord Dibben Excel MVP