| View previous topic :: View next topic |
| Author |
Message |
henskeleinen
Newbie
Joined: 15 Aug 2007
Posts: 2
|
 Posted: Wed Aug 15, 2007 1:51 am Post subject: [SOLVED] easiest way to make cell values non-negative? |
 |
|
Say I have a huge sheet and it has positive and negative real numbers in the cells.
I want to change all of sheets values to positive quickly. What is the easiest way to do this?
What about doing the same thing for some selection, not for the whole sheet?
Last edited by henskeleinen on Wed Aug 15, 2007 11:19 am; edited 1 time in total |
|
| Back to top |
|
 |
jrkrideau
Super User
Joined: 08 Aug 2005
Posts: 6733
Location: Kingston ON Canada
|
| Posted: Wed Aug 15, 2007 2:51 am Post subject: Re: easiest way to make cell values non-negative? |
|
|
| henskeleinen wrote: | Say I have a huge sheet and it has positive and negative real numbers in the cells.
I want to change all of sheets values to positive quickly. What is the easiest way to do this?
What about doing the same thing for some selection, not for the whole sheet? |
I am not sure that there is an easy way in Calc but you could set up another sheet the same size and use the equation =IF(A1<0;A1*(-1); A1) to do it.
Note this needs the sheet names added. I just ran a tiny checking example in a single sheet. If you have an retangular dataset it might be faster and easier to export to another program.
_________________
jrkrideau
Kingston ON Canada
Currently using Windows 7 & OOo 3.4.0 and Ubuntu 12.04 & LibreOffice 3.5.2.2 |
|
| Back to top |
|
|
David
Super User
Joined: 24 Oct 2003
Posts: 5668
Location: Canada
|
| Posted: Wed Aug 15, 2007 4:06 am Post subject: Re: easiest way to make cell values non-negative? |
|
|
If not worried about formulas or formatting, just data, save as a CSV, then use some program such as Metafile text editor to replace all "-" with nothing, then load back the CSV into Calc.
David. |
|
| Back to top |
|
|
huwg
Super User
Joined: 14 Feb 2007
Posts: 890
|
| Posted: Wed Aug 15, 2007 5:28 am Post subject: Re: easiest way to make cell values non-negative? |
|
|
| David wrote: | | If not worried about formulas or formatting, just data, ... replace all "-" with nothing ... |
That actually works within Calc! |
|
| Back to top |
|
|
keme
Moderator
Joined: 30 Aug 2004
Posts: 2732
Location: Egersund, Norway
|
| Posted: Wed Aug 15, 2007 5:34 am Post subject: Re: easiest way to make cell values non-negative? |
|
|
| huwg wrote: | | David wrote: | | If not worried about formulas or formatting, just data, ... replace all "-" with nothing ... |
That actually works within Calc! |
But then you might change formulas too, with unpredictable results... |
|
| Back to top |
|
|
acknak
Moderator
Joined: 13 Aug 2004
Posts: 4295
Location: ~ 40°N,75°W
|
| Posted: Wed Aug 15, 2007 6:04 am Post subject: |
|
|
Going off huwg's suggestion, you can use Find & Replace like this:
Search for: ^-.*
Replace with: =abs(&)
Options: Regular expressions = ON
Options: Current selection only = ON
That will 1) preserve the original data, 2) not change formulas and 3) allow you to limit where the changes take place. |
|
| Back to top |
|
|
JohnV
Administrator
Joined: 07 Mar 2003
Posts: 8984
Location: Lexinton, Kentucky, USA
|
| Posted: Wed Aug 15, 2007 10:58 am Post subject: |
|
|
Yet another way. Select area or entire sheet, right click > Format Cells > and in the Format Code box enter:
0.00;0.00
The 1st code formats positive numbers, the 2nd handles negatives and a 3ed can be used for zero values. Again these just affect the display and not the underlying value. |
|
| Back to top |
|
|
henskeleinen
Newbie
Joined: 15 Aug 2007
Posts: 2
|
| Posted: Wed Aug 15, 2007 11:16 am Post subject: |
|
|
| acknak: this solved the problem - thank you! |
|
| Back to top |
|
|
|
