I have had this problem too, many a time.
The best thing I can suggest is that, instead of typing "=" in the beginning of the formula, click on the = button just to the left of the little edit line. This will bring up a formula editor dialog.
That doesn't always work, but it seems to work most of the time.
There is something goofy with Excel in that it shows, sometimes, the formula even though, yes, indeed, you began the formula with "=". In fact, even if you cut and paste from a working cell to another, sometimes the formula works and the output is shown, sometimes the formula, complete with leading "=", just appears.
But it doesn't always happen when cutting and pasting. My guess is some as yet unknown way to reproduce in which Excel is thinking you're pasting a string instead of a formula, even though you copied it from another cell where it worked as a formula.
Anyone wanting to explore further might make permutation tests of source and destination cells being text or not, and copying the source cell by way of selecting the sell and copying (marching ant lines around the cell) vs. editing the sell and copying the formula via text based highlight, as in a text editor. In fact...
Yes! That's it!
At least this combo works:
Source and destination cells are formatted as normal, not text. Enter =4 + 5 in a cell. You see 9, and =4 + 5 remains, correctly, as the hidden formula.
Now convert it and a destination cell into text. Copy using either internal text edit copy or whole-cell copy (marching ants). Paste into destination, and wham! You see =4 + 5 instead of 9 as the cell's visible value.
Whew, that takes a load off my mind. I thought Excel was buggy instead of just merely clunkily designed.
To sum up my long-winded thought process of discovery, perserved for historical reasons above, somewhere between the copy and paste Excel gets confused and converts a formula into text.
This message was edited by AChimpNamedCornelius on 2002-08-23 13:52