Formula showing in the cell but not the result.

[Majen]

I have a formula in a cell and when I use the formula bar it shows me the correct answer but it doesn't show me it in the worksheet. Only the formula shows up.

The same formula could be another place in my worksheet and work fine.

I have tried changing format. Copying formula from another location and changing the information to fit my needs it won't show me the result.

Help!

Thank you.

[rikrak]

Majen,

is there a "=" missing?

today() instead of =today()

Henk

[Majen]

=MID(E68,4,2)+2& "X"& LEFT(E68,2)+4
here is the formula.

[Jim North]

I'm not sure what you a looking for as a result but if you put 1234 into E68

=MID(E68,4,2) & "2X"& LEFT(E68,2) & "4"

will give you 42X124

=VALUE(MID(E68,4,2)+2) & "X" & VALUE(LEFT(E68,2)+4)

will give you 6X16

Hope this helps!

[AChimpNamedCornelius]

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 ]

[rikrak]

you are right AChimpNamedCornelius

Then:
select the cell containing "=4 + 5"
press F2 to edit
make no changes and hit return
the result is "9"

Henk

[Majen]

Thank you very much it works now!!!

[shades]

I had this same problem earlier this week.

[PaddyD]

General method for fixing the following problem:

"All my formulas have ended up in text formatted fields & are held as text values. Even changing the format doesn't seem to help".

1) Go to - Edit | Go to | Special | Formulas. Press OK
2)Go to - Edit - Replace...
3) Find: =
4) Replace with : =
5) Click Replace all

In most cases, has the 'effect' of re-entering the formula(s), & getting Excel to recognise it as a formula not a text string.

Paddy

[XL-Dennis]

Paddy,

Thanks for that trick

Kind regards,
Dennis