Calc: Converting Formula to Value

classic Classic list List threaded Threaded
30 messages Options
Next » 12
Rich Shepard Rich Shepard
Reply | Threaded
Open this post in threaded view
|

Calc: Converting Formula to Value

   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

--
For unsubscribe instructions e-mail to: [hidden email]
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

Cor Nouws Cor Nouws
Reply | Threaded
Open this post in threaded view
|

Re: Calc: Converting Formula to Value

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,

--
  - Cor
  - http://nl.libreoffice.org


--
For unsubscribe instructions e-mail to: [hidden email]
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

Tom Tom
Reply | Threaded
Open this post in threaded view
|

Re: Calc: Converting Formula to Value

In reply to this post by Rich Shepard
Hi :)
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 :)


--- On Sat, 29/10/11, Rich Shepard <[hidden email]> wrote:

> From: Rich Shepard <[hidden email]>
> Subject: [libreoffice-users] Calc: Converting Formula to Value
> To: [hidden email]
> Date: Saturday, 29 October, 2011, 0: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 but
> cannot find the
> relevant information in the help pages. Please point me to
> instructions on
> how to do this.
>
> Thanks,
>
> Rich
>
> -- For unsubscribe instructions e-mail to: [hidden email]
> 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
>
>

--
For unsubscribe instructions e-mail to: [hidden email]
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

Stefan Weigel Stefan Weigel
Reply | Threaded
Open this post in threaded view
|

Re: Calc: Converting Formula to Value

In reply to this post by Rich Shepard
Hi,

Am 29.10.2011 01:14, schrieb Rich Shepard:
>   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




--
LibreOffice - Die Freiheit nehm' ich mir!

--
For unsubscribe instructions e-mail to: [hidden email]
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
Stefan Weigel Stefan Weigel
Reply | Threaded
Open this post in threaded view
|

Re: Calc: Converting Formula to Value

In reply to this post by Tom
Hi Tom,

Am 29.10.2011 01:21, schrieb Tom Davies:

> 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. ;-)

Stefan


--
LibreOffice - Die Freiheit nehm' ich mir!

--
For unsubscribe instructions e-mail to: [hidden email]
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
Tom Tom
Reply | Threaded
Open this post in threaded view
|

Re: Calc: Converting Formula to Value

Hi :)
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 :)
Rich Shepard Rich Shepard
Reply | Threaded
Open this post in threaded view
|

Re: Calc: Converting Formula to Value

In reply to this post by Cor Nouws
On Sat, 29 Oct 2011, Cor Nouws wrote:

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

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

--
For unsubscribe instructions e-mail to: [hidden email]
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

Rich Shepard Rich Shepard
Reply | Threaded
Open this post in threaded view
|

Re: Calc: Converting Formula to Value

In reply to this post by Tom
On Sat, 29 Oct 2011, Tom Davies wrote:

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

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

--
For unsubscribe instructions e-mail to: [hidden email]
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

Rich Shepard Rich Shepard
Reply | Threaded
Open this post in threaded view
|

Re: Calc: Converting Formula to Value

In reply to this post by Stefan Weigel
On Sat, 29 Oct 2011, Stefan Weigel wrote:

> This does not *convert* formula to value. It only changes the display. ;-)

Stefan,

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

Thanks,

Rich

--
For unsubscribe instructions e-mail to: [hidden email]
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

Rich Shepard Rich Shepard
Reply | Threaded
Open this post in threaded view
|

Re: Calc: Converting Formula to Value

In reply to this post by Tom
On Fri, 28 Oct 2011, Tom wrote:

> 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



--
For unsubscribe instructions e-mail to: [hidden email]
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

Rich Shepard Rich Shepard
Reply | Threaded
Open this post in threaded view
|

Re: Calc: Converting Formula to Value

In reply to this post by Stefan Weigel
On Sat, 29 Oct 2011, Stefan Weigel wrote:

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

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

--
For unsubscribe instructions e-mail to: [hidden email]
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

Uwe Koch Kronberg Uwe Koch Kronberg
Reply | Threaded
Open this post in threaded view
|

Re: Calc: Converting Formula to Value

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,
El 28/10/11 20:50, Rich Shepard escribió:

> On Sat, 29 Oct 2011, Stefan Weigel wrote:
>
>> The trick is to copy and then paste special (Edit menu). In the dialog
>> check only numbers.
>
> 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
>

--

Uwe Koch Kronberg
Enrique Koch y Cía. Ltda.
Fono: + 56 32 2543464
Fax: + 56 32 2543465
    Cel: + 56 9 87177837
E-mail:[hidden email] <mailto:[hidden email]>

La información contenida en este mensaje puede ser privada, confidencial
y estar protegida contra la divulgación. Si el lector de este mensaje no
es el receptor que se intentó contactar o un trabajador o interlocutor
responsable de entregar este mensaje al receptor que se intentó
contactar, usted está siendo notificado que cualquier propagación,
distribución o copia de esta comunicación está estrictamente prohibida.
Si ha recibido esta comunicación por error, por favor, notifíquenos de
inmediato respondiendo este mensaje y eliminándolo de su computador.


--
For unsubscribe instructions e-mail to: [hidden email]
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

The Wolfkin The Wolfkin
Reply | Threaded
Open this post in threaded view
|

Re: Calc: Converting Formula to Value

In reply to this post by Rich Shepard
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

--
I love gmail. Do you?
The Wolfkin


On Fri, Oct 28, 2011 at 7:50 PM, Rich Shepard <[hidden email]>wrote:

> On Sat, 29 Oct 2011, Stefan Weigel wrote:
>
>  The trick is to copy and then paste special (Edit menu). In the dialog
>> check only numbers.
>>
>
> 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
>
>
> --
> For unsubscribe instructions e-mail to: [hidden email].**
> org <users%[hidden email]>
> Problems? http://www.libreoffice.org/**get-help/mailing-lists/how-to-**
> unsubscribe/<http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/>
> Posting guidelines + more: http://wiki.**documentfoundation.org/**
> Netiquette <http://wiki.documentfoundation.org/Netiquette>
> List archive: http://listarchives.**libreoffice.org/global/users/<http://listarchives.libreoffice.org/global/users/>
> All messages sent to this list will be publicly archived and cannot be
> deleted
>
>

--
For unsubscribe instructions e-mail to: [hidden email]
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

"Ing. Jiří Hladůvka" "Ing. Jiří Hladůvka"
Reply | Threaded
Open this post in threaded view
|

Re: Calc: Converting Formula to Value

In reply to this post by Stefan Weigel
Copy the cells and paste as "unformatted text"
(use the icon for pasting and select "unformatted text")

:)



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

> Hi Tom,
>
> Am 29.10.2011 01:21, schrieb Tom Davies:
>
>> 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. ;-)
>
> Stefan
>
>

--
For unsubscribe instructions e-mail to: [hidden email]
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
Stefan Weigel Stefan Weigel
Reply | Threaded
Open this post in threaded view
|

Re: Calc: Converting Formula to Value

In reply to this post by Uwe Koch Kronberg
Hi,

Am 29.10.2011 02:05, schrieb Uwe Koch Kronberg:

> 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. ;-)

Stefan


--
LibreOffice - Die Freiheit nehm' ich mir!

--
For unsubscribe instructions e-mail to: [hidden email]
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
Stefan Weigel Stefan Weigel
Reply | Threaded
Open this post in threaded view
|

Re: Calc: Converting Formula to Value

In reply to this post by Rich Shepard
Hi Rich,

Am 29.10.2011 01:50, schrieb Rich Shepard:

>   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


--
LibreOffice - Die Freiheit nehm' ich mir!

--
For unsubscribe instructions e-mail to: [hidden email]
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
Rich Shepard Rich Shepard
Reply | Threaded
Open this post in threaded view
|

Re: Calc: Converting Formula to Value

In reply to this post by Stefan Weigel
On Sat, 29 Oct 2011, Stefan Weigel wrote:

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

Stefan,

   Thank you very much.

Rich

--
For unsubscribe instructions e-mail to: [hidden email]
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

Rich Shepard Rich Shepard
Reply | Threaded
Open this post in threaded view
|

Re: Calc: Converting Formula to Value

In reply to this post by The Wolfkin
On Fri, 28 Oct 2011, The Wolfkin wrote:

> 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

   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

--
For unsubscribe instructions e-mail to: [hidden email]
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

Tom Tom
Reply | Threaded
Open this post in threaded view
|

Re: Calc: Converting Formula to Value

In reply to this post by Rich Shepard
Hi :)
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 :)


--- On Sat, 29/10/11, Rich Shepard <[hidden email]> wrote:

> From: Rich Shepard <[hidden email]>
> Subject: Re: [libreoffice-users] Re: Calc: Converting Formula to Value
> To: [hidden email]
> Date: Saturday, 29 October, 2011, 0:48
> On Fri, 28 Oct 2011, Tom wrote:
>
> > 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
>
>
>
> -- For unsubscribe instructions e-mail to: [hidden email]
> 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
>
>

--
For unsubscribe instructions e-mail to: [hidden email]
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

Brian Barker Brian Barker
Reply | Threaded
Open this post in threaded view
|

Re: Calc: Converting Formula to Value

At 15:43 29/10/2011 +0100, Tom Davies wrote:
>A1= 10
>B1= 20
>and
>C1= A1+B1
>then the spreadsheet would display C1= 200 ...

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

Brian Barker


--
For unsubscribe instructions e-mail to: [hidden email]
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

Next » 12