Ask Your Question

Formulas typed in a cell suddenly appear as text. [closed]

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

Paulsghs gravatar image

updated 2020-08-11 12:17:52 +0200

Alex Kemp 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 reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-07-21 06:47:46.383677

2 Answers

Sort by » oldest newest most voted

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

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

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

Jim K gravatar image

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

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 +0200 )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 +0200 )edit

Question Tools

1 follower


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

Seen: 536 times

Last updated: Feb 14 '18