Ask Your Question

Formulas typed in a cell suddenly appear as text.

asked 2018-02-12 23:46:05 +0100

Paulsghs gravatar image

I'm trying to create a line chart which shows the intersection of circulator pump output curves with the load line of a network of pipes. I have to copy either text, blanks (" "), or positive numeric values from many cells in sheet "Source" to another sheet "Result" for the purpose of constructing a line chart. In cell Source.A2 I have some text, specifically "NRF-36" a pump model. When I type "=$Source.A2" in cell A2 of Sheet "Result" the text string "=$Source.A1" appears in cell Result.A2 instead of the text "NRF-36" from cell Source.A1.

If I can get the contents of Source.A2 to copy to Result.A2 then I have to figure out how to avoid copying negative values. I was thinking of using =if(Source.A2>0, Source.A2, if(Source.A2=" "," ",Source.A2)).

My greyhounds appreciate your help. PaulsGHs.

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2018-02-13 15:59:47 +0100

Jim K gravatar image

updated 2018-02-13 16:06:09 +0100

Go to View -> Show Formula and see if it fixes the problem.

The formula works for me.

=IF($Source.A2>0;$Source.A2;IF($Source.A2=" ";" ";$Source.A2))

However, it does the same as =$Source.A2. Perhaps this instead:


To have Calc fill in the address, select a cell on Result sheet, press = and then select the cell on Source sheet.

edit flag offensive delete link more


The original formula copies text entries in addition to positive numerics, it does not copy negative numerics.

Paulsghs gravatar imagePaulsghs ( 2018-02-14 10:41:55 +0100 )edit

Incorrect. If it's negative, then both conditions will be false, producing Source.A2 as seen in the "OtherwiseValue" at the end of the formula. An example: =IF(1=2;"Yes";IF(1=3;"Yes";"No")) produces "No". Documentation: IF.

Jim K gravatar imageJim K ( 2018-02-14 20:14:56 +0100 )edit

answered 2018-02-14 10:46:33 +0100

Paulsghs gravatar image

I found the problem. The cells were formatted as text. When I changed the cell format to number > general it worked properly.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-02-12 23:46:05 +0100

Seen: 28 times

Last updated: Feb 14