Calc: Converting Formula to Value

users
#1

I have a column where the cells display the sum of two other columns. I
want to change the contents from formula to value but cannot find the
relevant information in the help pages. Please point me to instructions on
how to do this.

Thanks,

Rich

#2

Hi Rich,

Rich Shepard wrote (29-10-11 01:14)

I have a column where the cells display the sum of two other columns. I
want to change the contents from formula to value ...

It is in
Tools > Options > Calc > View .. section display.

Cheers,

#3

Hi :slight_smile:
There is documentation on
http://wiki.documentfoundation.org/Documentation/Publications

I think you just need to go to

Tools - Options - "LibreOffice Calc" - View

and then UNtick the "Formulas" tick box in the "Display" section at the top of the 2nd column.
Regards from
Tom :slight_smile:

#4

Hi,

  I have a column where the cells display the sum of two other
columns. I
want to change the contents from formula to value but cannot find the
relevant information in the help pages. Please point me to
instructions on
how to do this.

The trick is to copy and then paste special (Edit menu). In the
dialog check only numbers.

Stefan

#5

Hi Tom,

Tools - Options - "LibreOffice Calc" - View

and then UNtick the "Formulas" tick box in the "Display" section at the top of the 2nd column.

This does not *convert* formula to value. It only changes the
display. :wink:

Stefan

#6

Hi :slight_smile:
It depends what Rich means by "change".

There are other possibilities too, such as perhaps the cell has been defined
as text, perhaps
'= blah blah
in which case removing the ' might do the trick.

I think that between us we have answered the most likely reasons that would
give Rich formulas rather than values.
Regards from
Tom :slight_smile:

#7

Cor,

   In the display section is a checkbox that allows the formula or value to
be displayed in each cell. What I want is to have the displayed value
_replace_ the formula in each cell. Can this be done?

Thanks,

Rich

#8

Tom,

   Yes, that changes the display, but not the cell contents. I need to change
the contents to the displayed value so I can delete the columns whose sum
(row-by-row) comprise the formula.

Thanks,

Rich

#9

Stefan,

   That's correct. How can I convert the formula value in each cell to the
value itself?

Thanks,

Rich

#10

It depends what Rich means by "change".

   Show the value in each cell, not the display of the formula. That is, when
the cursor is on a cell the entry widget along the top should display the
value, not the formula.

There are other possibilities too, such as perhaps the cell has been
defined as text, perhaps '= blah blah in which case removing the ' might
do the trick.

   No, when the formula was defined the cell contents were both numeric so
the formula value is also numeric.

I think that between us we have answered the most likely reasons that
would give Rich formulas rather than values.

   Actually, not. I need to know how to convert the formula to its value.

Thanks,

Rich

#11

Stefan,

   I'm not following you. Are you writing that I can highlight two adjacent
cells and have their sum pasted in the same row in a third column?

Rich

#12

Hi Rich,

Stefan means to select the area, copy it, go to another cell, choose
edit from the menu, click on paste special and inside the menu box it
opens unclick paste all, then unclick all the options below (text, date
and hour, formulae, comments, formats, objects) except for numbers,
which is the only option that should stay checked.

Good luck,

#13

So if I'm understanding you're trying to say start out with cells..
input a formula like this
http://minus.com/lgIwyrkPUPHj7
which gives a solution like this
http://minus.com/l50sfqIxwhBlz

and then now change the contents of cell D5 from {=B5+C5} to {80}

so that you can delete the contents of B5 an C5 yet keep the 80

#14

Copy the cells and paste as "unformatted text"
(use the icon for pasting and select "unformatted text")

:slight_smile:

Dne 29.10.2011 1:28, Stefan Weigel napsal(a):

#15

Hi,

Stefan means to select the area, copy it, go to another cell, choose
edit from the menu, click on paste special and inside the menu box it
opens unclick paste all, then unclick all the options below (text, date
and hour, formulae, comments, formats, objects) except for numbers,
which is the only option that should stay checked.

Hm. Not exactly. In order to "convert" a formula into its result
value, I wouldn´t go to *another* cell but paste special right in
the *same* cell --> that means overwriting the formula by its result
value. :wink:

Stefan

#16

Hi Rich,

  I'm not following you. Are you writing that I can highlight two
adjacent
cells and have their sum pasted in the same row in a third column?

No. Follow this:

(1) Select the cell that contains the formula

(2) Choose Edit | Copy

(3) Choose Edit | Paste Special

(4) Unselect "Paste all" and unselect "Formulas" and select "Numbers"

(5) Hit "OK"

Done.

Stefan

#17

Stefan,

   Thank you very much.

Rich

#18

I tried this but found that the cells (in your case with '80') were filled
with error messages when the source cells were removed. Changing the display
of the cell contents is easy.

Thanks,

Rich

#19

Hi :slight_smile:
So you want a static value instead of a formula? If any of the values that the formula is based on were to change then the value in the cell would not change.
eg if
A1= 10
B1= 20
and
C1= A1+B1
then the spreadsheet would display C1= 200 but you would still be able to see the formula in the top entry widget. A printout would not show the formula, it would just show the value. If you changed the value of A1 to 30 then C1 would now show the result as being 600.

If you were to set the formula bar (the entry widget) to show the value of C1 as 200 then changing A1 to 30 would have no effect on C1, it would still show the value 200.

In effect the formula bar / entry widget shows what some people might think of as coding that lays under the surface of what is being displayed

You can copy&paste an entire column or row as fixed values to another column or row by using "paste special" and that might be a useful way of seeing if any of the calculated values change over time.
Regards from
Tom :slight_smile:

#20

Is this perhaps what's known as the "new math"?

Brian Barker