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?example.ods
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.
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
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.
You may have set option
View -> Show Formula.
Solution: Unset the option
Hope that helps.
Update - see my comment below:
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?
I’ve attempted to attach a spreadsheet exemplifying the problem.
“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
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 way to eliminate it?
Yes - see the answer above, and even if you can’t imagine, there are users (like me) who love the option to quickly show the formulas being used in a sheet.
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
As I know it: There is not “Ctrl” key on the MAC keyboard. The existing key is the “Cmd”: ⌘
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`
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
- you first have to remove the direct formattinng, (right click into the cell)
- re-apply the fomula (toolbar equal sign)
- 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!”
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.
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)
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.
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.
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.
- You first specified via the direct formatting that the Cell should display Text .
- Then you enter a fomular and expect the cell to magically know what todo.
- 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 why it behaves tha way.
Don’t mix Text with Fomulas
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 (22.214.171.124) 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 "@
3. I was not disappointed because Excel did exactly as the instructions stated and as I wanted.
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, I understand the problem.