Re: [libreoffice-users] Re: Formulas in text instead of result - LO Calc

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



On Fri, 13 Sep 2013 13:27:28 +0100
Brian Barker <b.m.barker@btinternet.com> wrote:

> At 13:41 13/09/2013 +0200, Paul Steyn wrote:
> > 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.

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


-- 
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted