Excel will only display formula, not result ??? 1

[colinnwn]

Hi,

This is slightly embarrassing, but in 10 years of using Excel I never had this problem until the last month. Now it keeps happening on 2 computers in Office 97 and XP. When I type in a formula, the formula is displayed in the cell contents, and not the calculated value !?!

For instance I type, =concatenate(A1,A2), and instead of the 2 cells being concatenated together in A3, A3 shows "=concatenate(A1,A2)". Under tools>options>view>window options>formulas, is not checked. I searched around on google and here and did not find anything, but I may have worded the query poorly.

If anyone can point me in the right direction to show me some calculation, I would be most appreciative!

Thanks, Colin.

 

[AnotherHiggins]

Is this file imported from another application? Did you insert a column in which to place this formula?

I've seen the same behavior and nearly pulled my hair out trying to find the solution. Actually, I think that might have been my first experience with Tek-Tips, where I finally got a solution. As you can tell, I haven't been able to leave this place since.

Assuming your file is imported, try the following:
[ul][li]Click in a cell to the right of all imported columns - do not just click in a cell in any columns that you might have inserted[/li]
[li]type 1 (the one should be right-aligned: this indicates that the cell is formatted as a number)[/li]
[li]Copy this cell[/li]
[li]Select the cells that are giving you trouble[/li]
[li]Go to Edit > Paste Special > Multiply[/li]
[li]Now try to enter your formula[/li][/ul]

This happens because the fields in the imported file are formatted as text, regardless of how excel says they're formatted. A great way to see this for yourself is to try to change the formatting of any number cells that might be in the imported file.... Let's say you have a date in the file, "6/20/05". Try changing the formatting of that cell to YYY-MM-DD. It won't work. Multiplying the range my one forces excel to really make numerical cells numbers. Text fields will not be affected.

NOTE: any null (empty) cells that are selected during the Paste Special will be changed to a "1". So if you have already inserted a column, delete it before following the above instructions. After you have done the above you should be able to insert a column and have formulas work normally.

[tt]_____
[blue]-John[/blue]
[/tt][red]"I'm against picketing, but I don't know how to show it."[/red]
-Mitch Hedberg

Help us help you. Please read FAQ181-2886 before posting.

 

[xlbo]

John - I think you are on the right track but slightly off on the actual cause.

This behaviour is caused, simply, by having a cell formatted as TEXT and then trying to enter a formula into it

This is a timing issue generally and has 2 solutions open

1: With the formula already entered, format the cell as "General", go "into" the cell by clicking in the formula bar and press return - this should force the formula to calc.

2: format all cells as general and re-enter formulae completely

If you enter a formula THEN format as text, you will see the result as expected. If you format as TEXT and THEN enter a formula, you will see the results that you have reported.

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[colinnwn]

Thanks to anotherhiggins and xlbo for your contributions.

All 3 methods to fix the problem worked, but xlbo correctly identified the cause. At first I was dismayed because I would have to enter each cell or reenter all formulae. Then I realized I could do it exactly like I originally entered the formula, once, then fill down. All is well now.

To answer anotherhiggens questions...
I am not sure how the file was created, it was provided as an excel file from a vendor. You were correct, I was inserting a column to enter the formula.

Thanks again.
Colin.

 

[xlbo]

That would be why it was formatted as text then. The column to the left of the inserted column will be formatted as text and because excel is "clever" it decided that you would want the same format applied to the new column as well !!

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question