This Is Weird! - Formula Displayed as, er, a Formula??

[kyjae]

Hi All,

This is a weird one I've never come across before. I'm using Excel 2010 and referencing cells to other cells in another worksheet. All of a sudden what is displayed is not the content of the referenced cell but the actual formula used! My Excel is fully up-to-date and "Show Formulas" is not enabled.

If you look at the screen shot below I've uploaded, you'll see the cell I've selected works perfectly with the formula used shown in the formula bar. After that it all goes very weird!

Has anyone experienced this and if so, is there a fix?

Many thanks

formula error.JPG

[AliGW]

I've seen this myself a few times. See if you have some spaces added after the formula in the cell and delete them. failing that, try changing the cell format to general.

[gmr4evr1]

Make sure the cells that have the formula are not formatted as text. If they are, you will have to delete the formulas that are showing, format the cell as general then drag the formulas down again.

[shg]

I speculate that the cells the formulas are referencing are formatted as Text, and the cells that contain the formulas are formatted as General. If that's true, then Excel, always trying to be helpful, is changing the format of the formula cells to the format of the referenced cells. As you have found, that's not really helpful in this situation.

So: Change the format of the formula cells to something OTHER than General, e.g., Number, and it won't happen again.

[gmr4evr1]

....something OTHER than General....

Did I make a mistake by telling OP to change format to general?

[shg]

Yup .

[kyjae]

Guys,
It was a simple formatting issue. Many thanks to you all

[shg]

@gmr4evr1,

Format A1 as Text and enter a string. Format B1 as General and enter a formula:

Row\Col	A	B	C
1	Bob	BobBob	B1: =A1&A1

Now re-enter B1:

Row\Col	A	B
1	Bob	=A1&A1

If you format B1 as Number, that won't happen.

[gmr4evr1]

shg,
The only time the formula (=A1&A1) showed in B1 is when I formatted it as Text first, the other times, formatting as General 1st then Number and re-entering the formula as you suggested, the result was BobBob both of those times.

[shg]

I don't know of any setting in Excel that toggles that behavior. For me, it behaves exactly as described in Excel 2003, 2007, and 2010. The format of the formula cell will change ONLY if the formula cell's format is General.

It's the same behavior you get when a cell formatted as General references a cell formatted as Currency -- the formula cell's format changes to Currency. You don't get that behavior either?

[gmr4evr1]

I think I get what your saying now. Yes, when I have a formula in a cell that is formatted as general and it references a cell of another format, the cell with the formula does change the referenced cells format, but it still shows the result of the formula, not the formula itself.


When I answered OP, it seemed to me that the cells that are showing the formula instead of the result were formatted as Text prior to entering the formula. I know that doing that will result in the formula being shown.

Edit* I just noticed that if the cell with the formula changes to Text format and I change the formula, the cell shows the formula instead of the result. I never came across this before because I guess I never changed a formula in a cell once it was there....that is even weirder huh?

[shg]

I think I get what your saying now. Yes, when I have a formula in a cell that is formatted as general and it references a cell of another format, the cell with the formula does change the referenced cells format, but it still shows the result of the formula, not the formula itself.

Right -- the formula cell takes on the format of the referenced cell (Currency). Cells formatted as Currency show the result of their formulas, not the formula itself.

But if the referenced cell is formatted as TEXT, and the formula cell takes on THAT format, it shows the formula, not the result of the formula.

I'm surprised you can't duplicate the result.

[gmr4evr1]

shg,
See Edit* in post #11

[gmr4evr1]

I just noticed something else.....if the cell with the formula is formatted as General and it references more than one cell with different formats, the formula cell stays as general....could be another reason I never came across this before?

[shg]

Maybe. Most people don't notice it because the behavior is usually what they want, without ever thinking about it. This is just the corner case where it is never helpful.

[gmr4evr1]

Thanks for the info shg.....I learned something new today.

[shg]

That makes it a mutual success. You're welcome.