Formulas in text instead of result - LO Calc

users
#1

Open Calc, go to Tools > Options. In the Options dialog click on the plus
sign to the left of LibreOffice Calc (on the left hand tree) and then on
View.
Uncheck the option on the top right Display > Formulas

Hope this helps.

#2

Thanks to all. It is amazing how such a little action can cause so much
anxiety.

David Stuckey, MBA. MHSA.

#3

Hi All,

I've just had the same thing. I was working on a file, and found that
the formulas I put in would only show up as formula values, instead of
results. I made sure that the option for Display > Formulas was
unchecked, as suggested below, but this did not change anything. I even
tried checking it, then unchecking it, but neither one did anything.

As I was using LO 4.0.3.3 I tried upgrading first to 4.0.5.2, and then
4.1.1.2; they all exhibit the same behaviour. In 4.1.1.2 when you enter
a formula you get a blank cell, but if you change the cell formatting
it shows up as the formula value, instead of the result, and this
persists even if you then change the cell format back to what it was,
or even use undo. The previous versions all showed up as the formula
value when you first put it in, not a blank cell (IIRC).

Then, just to be sure, I tried in a new, blank spreadsheet, and it was
fine. It's only when working in this one file that I have a problem.
The file is a csv file. But surely, *surely*, that can't be the
problem? I know any formulas can't be _saved_ with a csv file, but I
need to do some quick calculations on the values in the file, and have
no intention of saving them, and if I did, I would obviously have to
save as a different file type.

Anybody else know of this problem? Is it time to file a bug report? I
can't but see this as a bug. I can change cell backgrounds, font
colour, use bold, underlined or italic text (I checked), and this can't
be saved with csv files, so why can I do all that but not use formulas?
When saving I get warned about features that may not be saved with the
file, but when editing the file, the underlying type it is saved as
should make no difference to LO's ability to do calculations. At the
very least I should be warned as to why formulas are not working,
rather than have them just not show any results (or worse in 4.1.1.2,
just show a blank cell).

Thoughts?

Paul

#4

Make sure the cell is not formatted as text.
Format -> Cells, Numbers tab, under 'Category'

#5

Hi T. R.,

Thanks, but as I stated in my original email, changing the cell
formatting doesn't do anything. I've tried a number of different
formats, including percent, currency, number and text, as well as
clearing direct formatting.

That said, I did redo my experiment (for about the 10th time), but this
time I put the formula in a new column to the right of the existing
data, and it worked fine. However, when I do as previously, and make a
new column B, and put the formula in there, it still doesn't work.
Actually, now that I experiment, it seems to work in any column I put
the formula, existing or new, provided it isn't between the
original first two columns.

I created a minimal sample file (I would attach, but I think it will be
stripped out of the email?), and tried again. When I put the formula in
a column to the right of the existing data, it works, but when I create
a new column B and put the formula there, I get the formula value
instead of the result. When I save it and then open it in a plain text
editor, I see that in the column that worked, the result is saved, but
in column B the formula value is saved. However, when I then open the
file in LO again, now both columns show the result, although the
originally working one is of course just a number. And I can still not
get the result when putting a formula into a newly created second
column. I note that it works fine if I put it in column B *without*
creating a new column, but any newly created column between the
original columns A and B will not show a formula result.

This is very strange. It's as if creating a new column B somehow marks
it as not for formulas.

And I tested it in a normal ods file, and it doesn't exhibit this
behavior, only in csv files.

Any ideas anybody?

Paul

#6

Thanks, but as I stated in my original email, changing the cell formatting doesn't do anything. I've tried a number of different formats, including percent, currency, number and text, as well as clearing direct formatting.

I won't guarantee this, but I think the damage is done when you enter a formula into a cell already formatted as Text when you have opened the spreadsheet from a .csv file. What you type is then interpreted and stored as text. *Changing* the format afterwards will not help: you need not to have had the format as Text when you typed the entry.

That said, I did redo my experiment (for about the 10th time), but this time I put the formula in a new column to the right of the existing data, and it worked fine.

That would be because your new column is not formatted as Text. Alternatively, what do you mean by "put" the formula? If you copy the existing formula - already fossilised as text - you will get text again, of course. But if you retype the formula into a non-text-formatted cell, won't you then obtain the formula you require?

However, when I do as previously, and make a new column B, and put the formula in there, it still doesn't work.

Hmm: perhaps that depends on how you create any new column: does it inherit the (troublesome) formatting of the existing column? Or perhaps by "put" you again mean "paste" rather than "type"? (These have different effects.)

Actually, now that I experiment, it seems to work in any column I put the formula, existing or new, provided it isn't between the original first two columns.

Look at the formatting of any relevant cells before you enter any formula: is it Text? Change it *first*.

When I save it and then open it in a plain text editor, I see that in the column that worked, the result is saved, but in column B the formula value is saved. However, when I then open the file in LO again, now both columns show the result, although the originally working one is of course just a number.

When you reopen the .csv file, your text string is evidently reinterpreted - as if it had been typed, not pasted - so it is converted to the formula you want.

Note that the formatting of cells has (at least?!) two effects: how any stored value is displayed and how any entered (i.e. typed) input is interpreted. Reopening a proper (.ods) spreadsheet file preserved the save cell contents, of course. Opening a .csv file seems to reinterpret it as if the contents were retyped. You could override this - the opposite of what you want - by selecting Text as the column type in the Text Import dialogue.

I trust the helps.

Brian Barker

#7

Hi Brian,

By golly, you got it in one! I've tested this with both a csv file and
an odt file, and they both work the same way. When you enter a formula
into a cell formatted as a number, you get the formula result, but when
you enter a formula into a cell formatted as text you get the formula
value. Any changes to the cell format *after* you have entered the
formula do not affect that. You need to change the cell format *before*
you enter the formula. If you change it afterwards, you need to
re-enter the formula to get the new representation.

It just so happens that the csv file had a date column as the first
column, which was defaulting to text format, so any new columns added
between it and the next column inherited the text format, but any other
column in the file had number format, which is why only that column
exhibited the strange behaviour.

There still seems to be a bug in 4.1.1.2 in that entering a formula
into a cell formatted as text gives you a blank cell until you make a
change to the spreadsheet (add a column, change the cell format,
something like that), and then like magic the formula value suddenly
appears in the cell. You can still see that the formula value is in
the cell from the text bar at the top, it just doesn't initially
appear in the cell itself. If the cell is formatted as a number you get
the formula result immediately. I'm pretty sure the previous versions
of LO didn't do this. I think I'll file a bug about just this.

This really isn't the way I thought it worked. I thought any cell
contents that started with an "=" made the cell a formula, and the cell
would always display the result of the formula, which could be
formatted in different ways. In order to have a cell that started with
an "=", you had to preceed it by "'" in order to make it display as
pure text. I thought the cell format only affected the format of the
displayed value, not the interpretation of the contents. I guess this
way makes sense too, but it's not necessarily intuitive, especially
when newly created columns inherit formatting. At least now that I know
I shouldn't have any further problems.

I see that preceeding formulae with "'" makes the "'" appear in the
cell, irrespective of the cell format. Seems this is no longer a text
mark character. I'm sure it used to be, or is in Excel, as I've used it
before. Can someone with Excel test if it works like that? Can someone
recall if LO used to work like that, and changed at some point? Am I
maybe remembering Quattro Pro or some such, and imagining that I've
used it since the DOS days?

See below for some further inline comments.

Thanks again Brian, and everybody who helped figure this out.

Paul

>Thanks, but as I stated in my original email, changing the cell
>formatting doesn't do anything. I've tried a number of different
>formats, including percent, currency, number and text, as well as
>clearing direct formatting.

I won't guarantee this, but I think the damage is done when you enter
a formula into a cell already formatted as Text when you have opened
the spreadsheet from a .csv file. What you type is then interpreted
and stored as text. *Changing* the format afterwards will not help:
you need not to have had the format as Text when you typed the entry.

Yup, as I've stated above, this is exactly correct.

>That said, I did redo my experiment (for about the 10th time), but
>this time I put the formula in a new column to the right of the
>existing data, and it worked fine.

That would be because your new column is not formatted as
Text. Alternatively, what do you mean by "put" the formula? If you
copy the existing formula - already fossilised as text - you will get
text again, of course. But if you retype the formula into a
non-text-formatted cell, won't you then obtain the formula you
require?

By "put" I do mean retype. I also tried with "paste only > formula",
but this also didn't work, either because, as you point out, it was
pasting from a cell that contained text instead of a formula, and
therefore copied the text instead as it had no formula to copy, or
because it was pasting into a cell formatted as text, and therefore
copied the formula in but interpreted it as text.

>However, when I do as previously, and make a new column B, and put
>the formula in there, it still doesn't work.

Hmm: perhaps that depends on how you create any new column: does it
inherit the (troublesome) formatting of the existing column? Or
perhaps by "put" you again mean "paste" rather than "type"? (These
have different effects.)

Again, bingo! The column was inheriting the text formatting of the
column to the left, which was a date, and therefore interpreted as text
when importing the csv file.

#8

By golly, you got it in one!

Good-oh!

There still seems to be a bug in 4.1.1.2 in that entering a formula into a cell formatted as text gives you a blank cell until you make a change to the spreadsheet (add a column, change the cell format, something like that), and then like magic the formula value suddenly appears in the cell.

Is this just a display problem? What happens, for example, if you minimise and then restore the Calc window: does that do anything?

I see that preceding formulae with "'" makes the "'" appear in the cell, irrespective of the cell format. Seems this is no longer a text mark character. I'm sure it used to be, or is in Excel, as I've used it before.

Surely it still works? There are a couple of points that may be confusing you here.

o If you have custom ("smart") quotes set in your AutoCorrect options, the apostrophe you type will be converted into an open single quote and will not fulfil its intended function. To correct this, either disable custom quotes or - more easily - just go to Edit

Undo (or Ctrl+Z) immediately after typing the apostrophe: this will

undo the autocorrection to leave the intended apostrophe.

o If you have already managed to enter something starting with an apostrophe or open quote in a cell in the same column, Calc may offer you suggested input, and you may need additional presses of Ctrl+Z to return to the plain apostrophe you need before typing the real cell contents you require.

I trust this helps.

Brian Barker

#9

Hi Brian,

Thanks again for your help.

>There still seems to be a bug in 4.1.1.2 in that entering a formula
>into a cell formatted as text gives you a blank cell until you make
>a change to the spreadsheet (add a column, change the cell format,
>something like that), and then like magic the formula value suddenly
>appears in the cell.

Is this just a display problem? What happens, for example, if you
minimise and then restore the Calc window: does that do anything?

Yep, just a display problem. Minimising and restoring Calc makes it
show up.

>I see that preceding formulae with "'" makes the "'" appear in the
>cell, irrespective of the cell format. Seems this is no longer a
>text mark character. I'm sure it used to be, or is in Excel, as I've
>used it before.

Surely it still works? There are a couple of points that may be
confusing you here.

o If you have custom ("smart") quotes set in your AutoCorrect
options, the apostrophe you type will be converted into an open
single quote and will not fulfil its intended function. To correct
this, either disable custom quotes or - more easily - just go to Edit
> Undo (or Ctrl+Z) immediately after typing the apostrophe: this will
undo the autocorrection to leave the intended apostrophe.

Hitting ctrl-z right after entering it doesn't change it, it removes
all the typing. The menu item has "Undo: Input", so it doesn't look
like an autocorrect is happening. I don't actually know where to set
this smart quotes you speak of. I had a look under AutoCorrect Options,
but nothing obvious leapt out at me. The Localized Options tab has
check boxes to replace single and double quotes, but the double quotes
one is ticked and the single quotes one is unticked for me.

Of course, when I try it with 0.00, it works. Entering 0.00 on its own
changes the value to just 0 (both in the cell and in the input line),
but entering '0.00 shows 0.00 in the cell ('0.00 is kept in the input
line). So it appears to be the way to enter numbers as text, but
doesn't perform the same function for formulae. Strange.

Regards

Paul