You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@openoffice.apache.org by Maggie Butler <mb...@southgaylord.com> on 2015/01/16 22:37:50 UTC
Convert formulas to absolute values
I have not been able to find a function which will convert a formula in a
spreadsheet to its absolute value. Is there one?
My use of this is to "freeze" 2014 financial calculations as history in the
2014 column, allowing the formula (now working in the new 2015 column) to
produce current results.
For example, if I type =ABS(N511/O511) -- where the value of N511=100 and
the value of O511=50 -- I want to see only the number 2 in the cell. Is
there a way to accomplish this?
Thanks for any insights. Maggie
Re: Convert formulas to absolute values
Posted by Brian Barker <b....@btinternet.com>.
At 14:37 16/01/2015 -0700, Maggie Butler wrote:
>I have not been able to find a function which will convert a formula
>in a spreadsheet to its absolute value. Is there one?
It cannot be a function, of course, as that would merely mean that
you had that function in the cell instead of the one you already have!
>My use of this is to "freeze" 2014 financial calculations as history
>in the 2014 column, allowing the formula (now working in the new
>2015 column) to produce current results. For example, if I type
>=ABS(N511/O511) -- where the value of N511=100 and the value of
>O511=50 -- I want to see only the number 2 in the cell.
You *will* see only the number 2 in the cell. But yes: you mean that
you want the cell to contain the simple number 2 rather than a
formula that evaluates to 2.
>Is there a way to accomplish this?
Yes: Paste Special is your friend.
o Copy the cell or range.
o Paste the result back over the original (or wherever you prefer),
but using Edit | Paste Special... (or Ctrl+Shift+V) instead of ordinary Paste.
o In the Paste Special dialogue, remove the tick from "Paste all" if
necessary, and ensure that Formulae is *not* ticked.
I trust this helps.
Brian Barker
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
For additional commands, e-mail: users-help@openoffice.apache.org
Re: Convert formulas to absolute values
Posted by Maggie Butler <mb...@southgaylord.com>.
Great, Brian! This removes the last barrier to my using Open Office for
ALL my gigantic spreadsheets. Many thanks, Maggie
On Fri, Jan 16, 2015 at 2:54 PM, Brian Barker <b....@btinternet.com>
wrote:
> At 14:37 16/01/2015 -0700, Maggie Butler wrote:
>
>> I have not been able to find a function which will convert a formula in a
>> spreadsheet to its absolute value. Is there one?
>>
>
> It cannot be a function, of course, as that would merely mean that you had
> that function in the cell instead of the one you already have!
>
> My use of this is to "freeze" 2014 financial calculations as history in
>> the 2014 column, allowing the formula (now working in the new 2015 column)
>> to produce current results. For example, if I type =ABS(N511/O511) -- where
>> the value of N511=100 and the value of O511=50 -- I want to see only the
>> number 2 in the cell.
>>
>
> You *will* see only the number 2 in the cell. But yes: you mean that you
> want the cell to contain the simple number 2 rather than a formula that
> evaluates to 2.
>
> Is there a way to accomplish this?
>>
>
> Yes: Paste Special is your friend.
> o Copy the cell or range.
> o Paste the result back over the original (or wherever you prefer), but
> using Edit | Paste Special... (or Ctrl+Shift+V) instead of ordinary Paste.
> o In the Paste Special dialogue, remove the tick from "Paste all" if
> necessary, and ensure that Formulae is *not* ticked.
>
> I trust this helps.
>
> Brian Barker
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
> For additional commands, e-mail: users-help@openoffice.apache.org
>
>