How come cells display formulas instead of results

English
,
#1

Simple stupid thing, but very frustrating!
I write a formula and the formula starting with the ‘=’ displays but the result of the formula does not!
View>formula IS NOT checked, other formulas display the results

Both: View> Formula IS NOT checked, flagged!
and
cells are formatted as number
ANY new formula even in blank columns show formula, not results
…
(My umpteenth attempt to fix it):
Not sure how/why, I went to the formula bar, put the cursor at the far left, hit the backspace key a few times, hit ENTER and that cell’s new formula displayed a result
Did that in other cells showing new formula, but that did not display the result
SO
copied and pasted the ‘fixed’ cell on to all the problem cells
They showed results
and now when I enter formula in those cells and anywhere in the sheet the formulas are displayed.
Makes now consistent sense.
Gremlins?

#2

See if Category in menu Format - Cell… - Numbers tab (the same that Ctrl+1) is set to Text (wrong option) or to Number (correct option).

EDIT: Also can use Ctrl+Shift+1 (to set Format as Number) or Ctrl+Shift+6 (to set Format Standard or General).

Add Answer is reserved for solutions. Please, click edit below your question to add more information.

#3

@Georgei, There was no spaces at the left of =? Have you tried with F9 (Recalculate) or PgUp and PgDn (to refresh the screen)?

Would you share a copy of the file without any compromissing data? Also, please, share your operating system and LibreOffice version. To do that, choose menu Help - About LibreOffice, copy from there, and paste in your question. Thanks.

#4

Please upload an ODF type sample file here.

#7

It’s been a while, and this problem is still a problem.
These possible solutions do not solve the problem:

  1. formatting the cell as number instead of text
  2. no spaces are to the left in the formula
  3. recalculate and hard recalculate make no difference
  4. updates to LO
  5. switching to a Mac and recreating the file from scratch!
    This may be related to Vlookup and =ax=bx not working. Vlookup gives N/A, =X=X gives false

The only solution I’ve found is to move to somewhere on the spreadsheet unused, create a forumla there, copy the formula to the problem cell, fix the formula to what is needed, and use fill as needed to populate other cells.
It’s as if a cell takes on an unseen format of Shtako, that needs to be overwritten for it to work, since that is not controlled by any setting in LO.

#8

Once a cell has been marked as text and then a formula entered into it it will stay displaying text even if the format is changed to Number until the cell is edited.
Edits such as backspacing the last character and re-typing it, or for multiple cells Text to Columns works well, as does Paste special Multiply (by 1) or a number of other edits.
See How to convert number text to numeric data

The Find and Replace given in the above link only works for apostrophe. The one below will also convert text to a formula in a cell that has been changed to Number format from Text.

  1. Select the column in which the
    digits are found in text format.
  2. Choose Edit > Find & Replace (Ctrl+H).
  3. In the Search for box, enter ^.
  4. In the Replace with box, enter &
  5. Check Regular expressions.
  6. Check Current selection only.
  7. Click Replace All.
2 Likes
#9

Turn on menu:View>Highlight Values.
If your formulas appear in green font, the cells have formulas. Goto menu:Tools>Options>Calc>View and turn off “Display Formulas”.
If your formulas appear in black font, the cells have strings. Select the cells and reset the formatting (Ctrl+M). Then keep the cells selected, call menu:Data>Text to columns… and click [OK]

#5

There are (at least) two reasons:

  • You toggled accidentally the feature View - Show formulas
  • Or maybe the cell was formatted as TEXT
#6

Not actally likely, but…
There is also the option >Tools>Options>LibreOffic Cal>View>>Display>>>Formulas which may wrongly be enabled.
If so it can be disabled the way @Zizi64 already told (toggling >View> Sow Formula), but will be active again for a new spreadsheet document.

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