Calc: Display formula results instead of the formula?

English
, ,
#14

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.

#15

… 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).

#16

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

#19

But you should have encountered this “problem”. Excel shows the exact same behaviour (since 1995 as far as I am concerned). It used to have a view option to show formulas instead of values and any text formatted cell interpretes any input as literal text.
And just like any other well designed application, Excel will not convert any value by mere change of a formatting attribute. After removing the text format, you’ve got to re-enter all contents.
With Calc, the following trick re-enters all contents:
menu:Edit>>Find&Replace…
Search: .+ (dot plus)
Replace: & (ampersand)
Other options:
[X] Regular expressions
[Replace All]

The text-to-colums method outlined on the Microsoft help page works similarly with Calc.

#20

August 3 2023 - 128p Mountain UnStandard Time [=GMT-0600]

Paul Wright via Ask LibreOffice
karolus via Ask LibreOffice
Andreas Säger via Ask LibreOffice

First, I was surprised to find emails regarding this 2020 topic. My solution back in 2020 was to forget LibreOffice Calc because of this issue. Using the solutions presented, it simply required too much time and effort.

I resorted to using my old Mac with Excel Version 4 (©1985…1992) where this problem just does not exist and I can create formulae the way I want and as I have for the past thirty-plus years - no hassle.

Perhaps some using LibreOffice Calc do not mind putting up with this, or they like it as a feature, from my perspective it is a time consuming obstacle. It renders LibreOffice Calc unusable from a practical, effective and efficient perspective.

Again personally, I would think that this issue, because it is an ever-present complaint in many forums, would have been addressed allowing spreadsheet users to disable it.

still “confused”

Villeroy
July 26

confused2:

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

But you should have encountered this “problem”. Excel shows the exact same behaviour (since 1995 as far as I am concerned). It used to have a view option to show formulas instead of values and any text formatted cell interpretes any input as literal text.
And just like any other well designed application, Excel will not convert any value by mere change of a formatting attribute. After removing the text format, you’ve got to re-enter all contents.
With Calc, the following trick re-enters all contents:
menu:Edit>>Find&Replace…
Search: .+ (dot plus)
Replace: & (ampersand)
Other options:
[X] Regular expressions
[Replace All]

Visit Topic or reply to this email to respond.

To unsubscribe from these emails, click here.

#24

Hi confused2
I responded to this thread because it was the top search result looking for a solution to formulas being stuck as text in LibreOffice Calc. I wanted to make sure the specific workaround for forcing cell content refresh was clear for others arriving on this thread, and wanted to see if this and the issue of Calc continuing to use cell reference highlighting even in text cells needed bug reports.
I use Linux, so Excel is not an option.

#39

Thanks for solution , the problem is view option :sweat_smile:

#3

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!”

1 Like
#4

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.

1 Like
#5

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)

#6

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.

1 Like
#7

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.

#8

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 why it behaves tha way.

tl;dr
Don’t mix Text with Fomulas :wink:

#9

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 "@

3. I was not disappointed because Excel did exactly as the instructions stated and as I wanted.

#10

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.

#36

I’ve been chasing this problem for days and fond that non of the proposed solutions work when I try them… To add more crazyness to this topic… Under the “View” option there is a selection labeled “Data Sources” That used to allow you to switch from viewing either the formula or the results. Now what is does is pop up at the top of the page called “Bibliography” and making that worse, it’s not obvious how to remove it… you need to go back and re-select “Data Sources” I also tried deleting the Lebreoffice app and re-installing… nothing got better. MAJOR BUGS IN THE CODE"
Somebody HELP!

#38

@Leop1 , View / Show formula.

#40

This post was flagged by the community and is temporarily hidden.

#42

There are 2 possible reasons, why a cell shows the formula instead of the formula result:
1 Toogle option menu:View>“Show Formulas” which affects all formula cells.
2a) The cell(s) in question were formatted as text (number format code @) when the formula has been entered. This special number format treats all keyboard input as literal text.
2b) Regardless of the number format, literal text can be entered with a leading apostrophe as in '=SUM(A1:A99). The apostrophe is not part of the text, therefore it is not shown in the cell, but you can see it in the formula bar or when the cell is in edit mode (key F2).

Toggle option menu:View>“Highlight Values” [Ctrl+F8] highlights all formulas in green font. If the cells in question show a green font, the cells do have formulas, and you should toggle the view option.
If the formula is shown in black font color, the cell does not have any formula for reason 2.1 or 2.2.
In case of 2.1 (number format code), remove the special number format, hit F2 to edit the formula, append a space (dummy edit) and hit Enter.
In case of 2.2 (literal text with leading apostrophe), edit the cell and remove the leading apostrophe.

For mass editing literal text:
Select the cells in question.
Make sure they have any number format but not text.
menu:Find&Replace…
Search .+ (dot plus)
Replace: & (ampersand)
Options:
Check “Current selection only”
Check “Regular expresion”
Button [Replace All]

#41

please learn to write a correct formula by checking your inputs/cell contents and distinguish either text or number or formula or emptyness!

  • maybe helpfully:
    • delete all the manual formattings, format→delete direct formatting, getting a clean cell so you can see text, formula are left aligned and number is right aligned
    • switch off: settings→display: formula

example_returno_041439.ods (20.7 KB)

added with:
«your biggest bug is your brain»
have a look to your mistakes and than to the correctives:
example_returno-2_063752.ods (23.2 KB)

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.