Showing cell contents as text in calc. (Not evaluating the formula the text represents)

users
#1

Hi.
I want to enter {=MMULT(MINVERSE(A14:R31),S14:S31)} in a cell and to display this as the text "{=MMULT(MINVERSE(A14:R31),S14:S31)}" (without the "" quotes). Formatting the cell as text doesn't help. I thought once you could prepend with a ' to define the characters following as left aligned text but not show the '. This does not seem to work any more, there must be a simple solution I am missing.
Cheers, steve

#2

Thanks for the reply, please click into the cell and change S31 to S32. Do the contents change into a formula or stay as text.
Steve

#3

Hi Steve,
maybe I am in a mistake, but you need to do nothing special, write the text
{=MMULT(MINVERSE(A14:R31),S14:S31)} and enter.
Miguel Ángel.

#4

Maybe it's a difference between the different language settings, or the operating system. I'm using LO Version 3.6.6.2 (Build ID: f969faf) on Windows 7 64-bit, and my language setting is USA English.

First I copied the formula from below, including the curly brackets, and pasted it into calc. It behaved as a string, not a formula.
Next, I copied it again, and then changed the S31 to S32. Still behaved as a string.
Third, I typed the formula in from scratch. Still behaved as a string.
Fourth, I grabbed the handle on the bottom right corner of the cell and dragged down to copy. Still a string.

But about this time, I noticed that the first two cells had blank spaces after the end of the entry. the third and forth cells (the one I had typed from scratch, and the copy made by dragging down from that one) didn't.

Finally, I clicked into the fourth cell to change S31 to S32. When I did so, the curly brackets disappeared, the cell references took on colors, and the lines appeared around the cells included in the arrays. BUT - when I hit ENTER, the text became a string again, complete with the curly brackets that had disappeared while I was editing it.

Maybe if I knew something about the kind of math represented by these functions, I'd have a better clue. But I know nothing about that level of math.

-- Tim

#5

Hi.
I am on 3.6.3. I get the effect you describe under "Finally" in your reply all the time. Possibly this was an over complicated example. A more simple example would be to have the contents "=C4" (no "") in a cell as text displaying as =C4 and not equal to the contents of C4.
Steve

#6

Ditch the curly braces. They prevent the formula inside from being recognized as a formula.

#7

The thing is that I don't want it recognised as a formula. I want to enter that text as an example or say =C4 as another example and not have it recognised as a formula.
Steve

#8

I want to enter {=MMULT(MINVERSE(A14:R31),S14:S31)} in a cell and to display this as the text "{=MMULT(MINVERSE(A14:R31),S14:S31)}" (without the "" quotes). Formatting the cell as text doesn't help.

I think you can type this - complete with the braces - and have it appear as a text string and not be interpreted as a formula, in fact. If you wanted it to be an array formula, you would need not to enter the braces but to use Ctrl+Shift+Enter (instead of simple Enter) to complete the entry; the braces are then added automatically to the display you see in the Input Line (as well as in the cell if you have the display of formulae selected). You probably know that.

... please click into the cell and change S31 to S32. Do the contents change into a formula or stay as text.

That is indeed where you appear to have a problem: editing the text string makes Calc reinterpret it as an array formula, which is what you are trying to prevent.

I thought once you could prepend with a ' to define the characters following as left aligned text but not show the '. This does not seem to work any more, there must be a simple solution I am missing.

Yes, this does generally work, but I think there may be one of two problems here. It does seem that if you enter what would be a formula but prefix it with the apostrophe, the string remains text and not interpreted as a formula, but the apostrophe remains displayed. I don't know why this should be, but it clearly gets in your way.

Another point worth mentioning is that if you have Custom Quotes selected in AutoCorrect Options, then the apostrophe will be instantly converted to an opening single quote mark and will continue to display. To avoid this whilst retaining custom quotes, use Edit | Undo (or Ctrl+Z) immediately after typing the apostrophe to undo the automatic correction. Oh, but if you have existing leading custom quotes in the same column, you will see automatic suggestions and will need to Undo twice.

So how to do what you require? Here are some workarounds:

o Enter a space before the text. This will fool Calc!

o Enter the formula =T("{=MMULT(MINVERSE(A14:R31),S14:S31)}") instead.

I trust this helps.

Brian Barker

#9

The single-' works in front of numerals.

Sometimes it works in front of a formula and sometimes it doesn't. Typing a space before the "=" or the "{=" seems to work consistently instead. Good idea.

I just checked in LibO 4.0.1.2 on Windows XP SP3.

#10

If you put {=MMULT(MINVERSE(A14:R31),S14:S31)} in, say, A1. A2 with =FORMULA(A1), will display
{=MMULT(MINVERSE(A14:R31),S14:S31)} in A2.

David Lynch

#11

If you put that text into A1, I don't think that is true. The text itself is not a formula, so the result of the FORMULA() reference is #N/A. But yes: if you enter that into A1 *as an array formula* (without typing the braces and using Ctrl+Shift+Enter), that is indeed what happens. Then you have to remember, when you edit what is in A1 (as the questioner required), to use Ctrl+Shift+Enter instead of simple Enter each time to confirm the edit.

Brian Barker