Only the formula is displaying 1

[toekneel]

I've worked with Excel spreadsheets for a long time, and have never encountered this one.

I enter a formula into a cell. Instead of displaying the result of the formula, it is displaying the formula. When I embed that formula into another formula on another cell, the text of the formula again is displayed.

I know what your first thought is... go to Tools, Options, View and uncheck the "Formulas" option. It isn't checked!

I'm working on a file that was generated from an Access 2000 database by Analyzing in Excel. I inserted a new row at the beggining column of the file, and that formula had no problem. It was just when I was entering the formula in the middle of the existing file that the problem surfaces.

I can work around the problem by copying the formula line that was added at the beginning column of the file. I can then paste that formula in the midst of the other columns and it will calculate the formula that is then entered into it.

Seems like some kind of formatting may be applied to the file generated out of Access that is doing this?

 

[ETID]

It sounds to me like that cell or coloumn is formated as text....

try reformating to general

 

[Zathras]

Sounds like you're picking up a leading space. Look closely in the formula bar. Do you see an apostrophe?

If the formula is in cell A100 (for example), type in the formula [blue]

 =A100

[/color] somewhere and examine the result. you should see the same "formula" in the cell again but the formula "=A100" should appear in the formula bar.

 

[toekneel]

Are you referring to the column where the formula resides? That is true. Is it really as simple as that?

 

[toekneel]

I'm referring to the posting where the column is formatted as text as opposed to general. There is no leading space or apostrophe on this one.

 

[ETID]

If the column where the formula resides is formated as text, you will see it as typed, instead of the value it returns.

 

[KenWright]

If you insert a column or a row in Excel, and the column to the left or the row above where you just inserted contains text, then the row/column you have just inserted will take on the same formats and will themselves be formatted as text. You simply need to select the area and format as general before you enter any data.

If you already have a set of numbers in the cells that you want to convert to numeric, you cannot simply just change the format. The trick for this one is to copy an empty cell, select the affected area and do Edit / Paste Special / Add. This will coerce Excel into treating those cells as numeric.

If you have a set of formulas that are in the same state, then slightly different. Format the cells as general and then do an Edit / Replace, putting an = sign in both the Find and the Replace with sections. This will then coerce the formulas back to formulas and not text.

Regards
Ken.................