Ask Your Question
1

Calc: Display formula results instead of the formula?

asked 2020-08-31 21:20:08 +0100

con fused gravatar image

updated 2020-08-31 23:36:05 +0100

How do I prevent a cell from displaying a formula instead of the formula's result? And, how do I set an option so that I never have to deal with this issue again?C:\fakepath\example.ods

edit retag flag offensive close merge delete

Comments

And, how do I set an option so that I never have to deal with this issue again?

There is no such option. Sometimes you want see which formulas you are using. See also my answer.

Opaque gravatar imageOpaque ( 2020-08-31 22:31:13 +0100 )edit

2 Answers

Sort by » oldest newest most voted
2

answered 2020-08-31 22:29:05 +0100

Opaque gravatar image

updated 2020-09-01 11:31:34 +0100

Hello,

there are two different reasons why you don't see the result of a formula evaluation but the formula itself.

1. Formatting your cell as Text
If you format a cell as Text (Format Code: @) before you enter a formula, you disable any input recognition and you get your formula as text. If you don't do so, entering = tells LibreOffice that your are about to enter a formula.

Solution: Remove the text format (CTRL+M) and edit the formula.

2.View Option
You may have set option View -> Show Formula.

Solution: Unset the option

Hope that helps.

Update - see my comment below:

C:\fakepath\Formula-Shown-Modified.ods

edit flag offensive delete link more

Comments

to Opaque Thanks for your answer. However, my attempts using your instructions did not work. Also, CTRL +M closes a work sheet. CTRL +m seems to do what your intentions imply. Just a comment, but, I NEVER use keyboard command - always mouse and menu. This is because I use too many differing programs on my Mac, PC and Linux. Having to lift my hand from the mouse, except for data entry is quite frustrating.

I began using Excel in 1986 and have hundreds of specialty spread sheets on my Mac and never encountered this formula display problem. Surely there must be a way to eliminate it?

to igortius I've attempted to attach a spreadsheet exemplifying the problem.

con fused gravatar imagecon fused ( 2020-08-31 23:30:47 +0100 )edit

"Also, CTRL +M closes a work sheet. CTRL +m seems to do what your intentions imply. Just a comment, but, I NEVER use keyboard command - always mouse and menu."

From the menu: Format - Clear Direct Formatting

Zizi64 gravatar imageZizi64 ( 2020-09-01 06:41:22 +0100 )edit
1

However, my attempts using your instructions did not work.

Because you did not follow my answer, which stated in 1.Formatting your cell as Text, that you must not format the cells as text before you enter a formula, but you have Format Code:"plane "@, which clearly is a text format code and hence you get your formula as text. Remove the format and add the following formula:

="plane =" & $D$20 & E9 & $E$18 & F9 & $D$21 --or--
="plane " & $D$20 & E9 & $E$18 & F9 & $D$21
(not sure what you want)

See the update in my answer


I began using Excel in 1986 and have hundreds of specialty spread sheets on my Mac and never encountered this formula display problem

... and now you are using LibreOffice Calc, which isn't a clone of Excel.


Surely there must be a ...

(more)
Opaque gravatar imageOpaque ( 2020-09-01 10:50:24 +0100 )edit

... Cont

CTRL +M vs. CTRL+m

Ever since I can remember the standard was CTRL+M meaning: Press key CTRL + key labeled M at the same time: You introduce a meaning of: Press key CTRL + key SHIFT + key labeled M. That's wrong and each documentation would write CTRL+SHIFT+M for that (the nomenclature is addressing labels of keys and there is no key labeled m).

Opaque gravatar imageOpaque ( 2020-09-01 11:37:09 +0100 )edit

As I know it: There is not "Ctrl" key on the MAC keyboard. The existing key is the "Cmd": ⌘

Zizi64 gravatar imageZizi64 ( 2020-09-01 13:02:49 +0100 )edit
0

answered 2020-08-31 21:32:45 +0100

igorlius gravatar image

updated 2020-09-01 00:11:43 +0100

To make a cell dislay the result of a fomula, you have to start the cell content with a = (equal sign)

`=1+2` + Press `ENTER`

[update]

it seems to get the correct / expected behaviour the direct formatting has to be applied after the fomula evaluation.

To fix the sheet you supplied, you have to

  1. you first have to remove the direct formattinng, (right click into the cell)
  2. re-apply the fomula (toolbar equal sign)
  3. re-add the direct format.

That seems to work.

Hope that helps.

To show the community your question has been answered, click the ✓ next to the correct answer, and "upvote" by clicking on the ^ arrow of any helpful answers. These are the mechanisms for communicating the quality of the Q&A on this site. Thanks!

Have a nice day and let's (continue to) "Be excellent to each other!"

edit flag offensive delete link more

Comments

All of my formulas begin with an equal sign "=". When the cell is displayed, it includes the equal sign as well as the remainder of the formula.

con fused gravatar imagecon fused ( 2020-08-31 21:36:07 +0100 )edit

And the result are not displayed ? Could you share an example file, so i can take a look at it? (You can add it to your question via edit)

igorlius gravatar imageigorlius ( 2020-08-31 21:41:51 +0100 )edit

I updated my answer .. i think the order of operations is important, First the fomula has to applied and afterward you can add/change the cells format eg. add "plane " suffix. I just noticed that this more or less is the same as what Opaque said, ... but maybe my description is easier / more hands on to the supplied example sheet. If it is still unclear, just leave another comment and i can add some screenshots.

igorlius gravatar imageigorlius ( 2020-09-01 00:13:41 +0100 )edit

igorlius I followed your instructions. They worked - one formual at a time. Thank you.
However, I have about 35 similar cells on the spread sheet from which I took the example and don't have enough time in a day to do each one individually. I need to be able to use some command or option that removes this annoyance from the whole spread sheet as well as many other spread sheets I have.

I'm wondering why anyone would design a spread sheet to show a formula instead of its result? If someone tells me the circumference of a tree and asks me to tell them the diameter, I don't present them with the formula d=C/pi: worse yet =A1/PI(). I give them the result.

con fused gravatar imagecon fused ( 2020-09-01 01:16:56 +0100 )edit

If the cells are all in one row or colum, i think you can remove the direct format for the entire row / column in one step. And Re-Applying the direct format should also works in one step.

Only applying the cell re-evaluation of the formula without interacting with the toolbar or cell does not seem to have an bulk operator that is equally convinient. At least i could not find any.

Regarding the why LO-calc operates that way, ... think about it like this.

  1. You first specified via the direct formatting that the Cell should display Text .
  2. Then you enter a fomular and expect the cell to magically know what todo.
  3. You are disappainted that the cell does what you told it todo in the first step.

Sounds a bit unreasonable doent it?

Even if you are not happy about how it work, at least now you have a clearer understandy ...(more)

igorlius gravatar imageigorlius ( 2020-09-01 02:05:30 +0100 )edit

to igorlius and Opaque
One important thing, I now realize, is that I forgot to mention that the spread sheet with which I'm working and suppled as an example was imported from Excel on a Mac to LibreOffice Calc (7.0.0.3) on a Linux LMDE computer.



LibreOffice Calc is not an Excel clone. However, on LibreOffice's web page and others that discuss LibreOffice, comparisons to Microsoft Office abound. The implication is implied that it, if not a clone, is nearly so.



When I created the original spread sheet in Excel, I
1. entered the formula - no preformatting
---a formula in Excel can consist of numbers, text or a combination. It is the ampersand operator that converts any numbers to text.
2. I did not expect Excel to "magically" know what to do
---after entering the formula, I applied the formatting I desired: align right and "plane ...(more)

con fused gravatar imagecon fused ( 2020-09-01 20:08:30 +0100 )edit

It was in the conversion from Excel to Calc that the problem surfaced. And, since I have a lot of Excel spread sheets dating back to 1986, I'm not enthused about converting them, although I had hoped to do so, to LibreOffice Calc.



Excel is not the only spread sheet program I've used. Over the years I've used Wingz, Mariner, Quattro Pro, Claris Works, Full Impact, Resolve, ESS and others. Based upon this background, having a formula displayed instead of its result was not a spread sheet problem I expected.



Out of this exercise, I did learn what the term "direct formatting" and its opposite means. From my perspective, in order for backwards compatibility, it would be nice to have a LibreOffice Calc option "Use Direct Formatting ONLY."



I do thank you both for the responses you gave. While not the solution for which I was looking ...(more)

con fused gravatar imagecon fused ( 2020-09-01 20:09:19 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-08-31 21:20:08 +0100

Seen: 183 times

Last updated: Sep 01 '20