Changing a Cell Reverts to Plain Text

tracy · December 6, 2022, 11:45pm

If I change one reference in a formula, it changes to plain text. E.g.: Below normally returns a zero. $R3 was changed to $Q3


=IF($BJ3<>"",IF($IL3<=$R3*5,IF($IM3<3,-1,IF($IM3<10,1,IF($IM3<14,2,IF($IM3<17,3,IF($IM3<19,4,IF($IM3<20,5,0)))))),0),0)
 
Cell value: 0
 
If I change $R3 to $Q3 it just shows the formula in plain text.
 
=IF($BJ3<>"",IF($IL3<=$Q3*5,IF($IM3<3,-1,IF($IM3<10,1,IF($IM3<14,2,IF($IM3<17,3,IF($IM3<19,4,IF($IM3<20,5,0)))))),0),0)
 
Cell value:  =IF($BJ3<>"",IF($IL3<=$Q3*5,IF($IM3<3,-1,IF($IM3<10,1,IF($IM3<14,2,IF($IM3<17,3,IF($IM3<19,4,IF($IM3<20,5,0)))))),0),0)

Or more simply, if I have a cell that says =5 it displays a 5.

But if I change it to =6, it shows =6

If another cell depends on it having a numerical value, it returns #VALUE!

I have tried changing the cell format from numbers to text, to scientific, to percent, back to number, or anything else in between, it doesn’t help.

File added. Presume you can ignore external cell lookups.

If you change cell Q4 from =1 to some other number (WITH the equal sign) I get the problem. If you just enter a number, it seems OK.

The other cell problem is JN4 (which references Q4).

By the way, I finally got it to work by retyping the whole cell from scratch reading the JN4 formula in a text editor.

So JL4 (and the rest of the JL column works after my manual edit.

WGQ1new.ods (314.0 KB)

LeroyG · December 7, 2022, 12:08am

Cell format as number must work.
Can you edit your question and share a sample file, just with the problematic cell?
Please, don’t use Suggest a solution to add more info. Thanks.

LeroyG · December 7, 2022, 1:12pm

imagen
Change Category to Number.

tracy · December 7, 2022, 1:59pm

Yes I notice will perform that action before data entry.

However if I edit the next cell below (Q4), THEN try to change the category to number, it fails. (It will still show the equal sign before the number.)

However, if I delete the data in the cell, change the category to number, then it will work. But not the other way around.

After more experimentation, a thing that works after data entry is the “format as number” icon on the tool bar. But if I use the pull down menu, it fails.

I am wondering if there is a setting in my config that when creating new sheets that defaults all cells to text?

LeroyG · December 7, 2022, 3:28pm

Changing the category will not change the cell content until you edit/change the cell content. You can try with menu Data - Text to Columns.

No. Just create a new Sheet and type any number.
With Ctrl+F8 you would see different content type in different colors: numbers in blue, and text in black.

By the way, to enter numbers, there is no need to add before an equal sign (=).

In the status bar, there is a hint if the cell content is number or not:
imagen

tracy · December 7, 2022, 4:13pm

Thanks. I think I have the hang of it now.

P.S. much of the data was imported from Excel. The equal signs were autopopulated on import, so I preumed to use them.