| View previous topic :: View next topic |
| Author |
Message |
unforeseen
General User
Joined: 26 Oct 2006
Posts: 7
Location: Switzerland
|
 Posted: Thu Oct 26, 2006 6:21 am Post subject: Replace values below a certain value |
 |
|
Hello
I have a huge table where I want to replace all values (floating point) which are below a certain value with another value (always the same, a 0 [zero]). I am not a hardcore spreadsheet user, so if anybody could show me the way, that was great!
OpenOffice 2.0.4 on Ubuntu Edgy beta. |
|
| Back to top |
|
 |
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Thu Oct 26, 2006 7:18 am Post subject: |
|
|
=$A1*($A1>limit)
or
=IF($A1>limit);$A1;0)
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org |
|
| Back to top |
|
|
unforeseen
General User
Joined: 26 Oct 2006
Posts: 7
Location: Switzerland
|
| Posted: Thu Oct 26, 2006 8:14 am Post subject: |
|
|
| Villeroy wrote: | =$A1*($A1>limit)
or
=IF($A1>limit);$A1;0) |
Thank you; I already tried that, but it gives me an error 522 for all fields which evaluate to true, which AFAIK means "endless loop".
I only need to do this once, the data won't be updated. Like a search and replace, but I must be able to specify a numeric comparison. |
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Thu Oct 26, 2006 8:36 am Post subject: |
|
|
| unforeseen wrote: | | Villeroy wrote: | =$A1*($A1>limit)
or
=IF($A1>limit);$A1;0) |
Thank you; I already tried that, but it gives me an error 522 for all fields which evaluate to true, which AFAIK means "endless loop".
I only need to do this once, the data won't be updated. Like a search and replace, but I must be able to specify a numeric comparison. |
This is meant to be put in another column. If you are really, really shure you want to drop your original data, copy the resulting column and Menu:Edit>"Paste Special..." with option "Numbers" but "Formulas" unchecked. If you are not 100% shure: simply hide column A and do further calculations based on the new column.
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org |
|
| Back to top |
|
|
unforeseen
General User
Joined: 26 Oct 2006
Posts: 7
Location: Switzerland
|
| Posted: Thu Oct 26, 2006 8:54 am Post subject: |
|
|
| Villeroy wrote: | | This is meant to be put in another column. If you are really, really shure you want to drop your original data, copy the resulting column and Menu:Edit>"Paste Special..." with option "Numbers" but "Formulas" unchecked. If you are not 100% shure: simply hide column A and do further calculations based on the new column. |
Yes, I am sure I want to drop the data; I made a new sheet for that.
OK, I get the idea, I put the formula in another field and somehow "shift" the whole table to another location, then copy the resulting data (without formulas) into a new table and I am set. That should work, I`m gonna test this tomorrow. However, I hoped there was a simpler method since I have 107 columns and 5576 rows packed with data. Expanding the Formula to all needed Fields is gonna take some time... 
Thank you! |
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Thu Oct 26, 2006 9:16 am Post subject: |
|
|
| unforeseen wrote: | | Villeroy wrote: | | This is meant to be put in another column. If you are really, really shure you want to drop your original data, copy the resulting column and Menu:Edit>"Paste Special..." with option "Numbers" but "Formulas" unchecked. If you are not 100% shure: simply hide column A and do further calculations based on the new column. |
Yes, I am sure I want to drop the data; I made a new sheet for that.
OK, I get the idea, I put the formula in another field and somehow "shift" the whole table to another location, then copy the resulting data (without formulas) into a new table and I am set. That should work, I`m gonna test this tomorrow. However, I hoped there was a simpler method since I have 107 columns and 5576 rows packed with data. Expanding the Formula to all needed Fields is gonna take some time... :?
Thank you! |
In Sheet2.A1:DC1 (107 columns) mark the columns to be transformed with a 1 (or anything not blank, not zero, not string).
Your data in Sheet1.A1:DC5576:
Select Sheet2.A1:DC5577 with A2 as active cell
=IF(A$1;$Sheet1.A1*($Sheet1.A1>0.5);$Sheet1.A1) >> Alt+Enter
Copy: Ctrl+C
Paste Special: Ins
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org |
|
| Back to top |
|
|
David
Super User
Joined: 24 Oct 2003
Posts: 5668
Location: Canada
|
| Posted: Thu Oct 26, 2006 10:11 am Post subject: |
|
|
| unforeseen wrote: | Expanding the Formula to all needed Fields is gonna take some time...
|
Take Villeroy's advice. If your data is continuous [all numerical, and in one area], you would need to write the formula only once in one cell. Then, by using the mouse and the lower right tag of the cell you just wrote, OR by selecting all and using Edit/Fill from the upper menu, you can have the spreadsheet automagically form all the others. You must be clear on whether or not to use absolute or relative references.
You might practice first on some smaller insignificant sample sheet.
David. |
|
| Back to top |
|
|
unforeseen
General User
Joined: 26 Oct 2006
Posts: 7
Location: Switzerland
|
| Posted: Fri Oct 27, 2006 12:12 am Post subject: |
|
|
Thank you guys, I just did set the formula to a cell outside the actual data range and dragged it to the size of the original table and the values filled in as expected. Then copying and pasting special (formulas unchecked) into another new sheet and I was set. So that definitively works.
I think OOo should have something like a "process data" dialog where you can process and change selected data while iterating over every cell. This solution worked for me, indeed, but it is more like a workaround. |
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Fri Oct 27, 2006 1:56 am Post subject: |
|
|
| unforeseen wrote: | Thank you guys, I just did set the formula to a cell outside the actual data range and dragged it to the size of the original table and the values filled in as expected. Then copying and pasting special (formulas unchecked) into another new sheet and I was set. So that definitively works.
I think OOo should have something like a "process data" dialog where you can process and change selected data while iterating over every cell. This solution worked for me, indeed, but it is more like a workaround. |
Nope, I have to disagree. Generally spoken, there is hardly any spreadsheet-functionality that generates new data or removes existing data. This has to be completely up to the user. Spreadsheets, generating or removing data would be even more dangerous than the already existing ones. Well, if you write macros generating/removing data then you know what you are doing and why, but the designers of this toolset can not know exactly. But they provide many tools (formulas and clipboard) you can combine for your specific purpose.
A decent database can show any sub-set of existing data, generate/remove data from existing data and it can grant/reject specific (groups of) users the permission to do so.
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org |
|
| Back to top |
|
|
unforeseen
General User
Joined: 26 Oct 2006
Posts: 7
Location: Switzerland
|
| Posted: Fri Oct 27, 2006 2:15 am Post subject: |
|
|
The points you mention have definitively to be considered. However, such a one-time action dialog to change data, maybe with the option to first duplicate the sheet, wouldn't be a bad thing IMHO.
Well, as a MySQL database developer maybe I approached my first serious use of a spreadsheet application with some quirky expectations.  |
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Fri Oct 27, 2006 2:48 am Post subject: |
|
|
| unforeseen wrote: | The points you mention have definitively to be considered. However, such a one-time action dialog to change data, maybe with the option to first duplicate the sheet, wouldn't be a bad thing IMHO.
Well, as a MySQL database developer maybe I approached my first serious use of a spreadsheet application with some quirky expectations. :) |
If your data origin from a database you may want to keep them in there and create an OOo-Base document (*.odb). It establishes a (login-)connection to your mysql and it can store queries and views for the users. If your db is registered, it is accessible from all other OOo-docs. For registered datasources hit F4 and drag some table or query into a document. OOo comes with a simple dBase-table "Bibliography" as an example and you can add several addresses from mail clients, csv and spreadsheets. Database-records, imported into Calc are seen as either one of refreshable database ranges or refreshable data pilots(aka pivot tables, aka cross tables).
OOo-dBase is read-write, indexable, but non-relational unless you connect through a third-party driver.
OOo-sources of type text/spreadsheet/addressbooks are readonly, without indices and non-relational.
The builtin type integrates a binary hsqldb into the *.odb-file. The builtin hsql currently has no user-administration, no privileges.
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org |
|
| Back to top |
|
|
unforeseen
General User
Joined: 26 Oct 2006
Posts: 7
Location: Switzerland
|
| Posted: Fri Oct 27, 2006 5:36 am Post subject: |
|
|
| Wow; after some hassle with non-working JBDC Class and trial and error, I`m now using the odbc-package to access my MySQL databases from OOo Base. Looks very promising, thank you!! |
|
| Back to top |
|
|
acknak
Moderator
Joined: 13 Aug 2004
Posts: 4295
Location: ~ 40°N,75°W
|
| Posted: Fri Oct 27, 2006 9:57 am Post subject: |
|
|
| Villeroy wrote: | | ... there is hardly any spreadsheet-functionality that generates new data or removes existing data. |
What about Find & Replace? The OP's request sounded to me like a "Find & Replace" function, only using a numeric expression rather than text. That would still be under user control and would seem like a useful extension to me.
Personally, I'm comfortable with the strategy suggested here, as that's what I've always used. It also has the advantage that you can do the operation and check the results before you decide to commit the changes and replace the original data.
But by that logic you could also make an argument for not needing the Find & Replace dialog, and I don't hear anyone saying that.
Just a thought. |
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Fri Oct 27, 2006 2:16 pm Post subject: |
|
|
| unforeseen wrote: | | I have a huge table where I want to replace all values (floating point) which are below a certain value with another value (always the same, a 0 [zero]). |
| acknak wrote: | But by that logic you could also make an argument for not needing the Find & Replace dialog, and I don't hear anyone saying that.
|
How to compose a regex, matching some value smaller than certainValue?
What if certainValue is irrational like PI and the values include negatives?
Having the data in a database already, you just have to define a query once for ever and drag it into calc. In the spreadsheet you call Menu:Data>Refresh or Data>DataPilot>Refresh in order to get a *linked* snapshot of the original data with all values <certainValue=0, omitting all unnessesary fields, sorted, grouped and with pretty good performance. Then your spreadsheet can do all the number-crunching, graphs and pretty formatting of tabular data, based on that snapshot.
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org |
|
| Back to top |
|
|
unforeseen
General User
Joined: 26 Oct 2006
Posts: 7
Location: Switzerland
|
| Posted: Fri Oct 27, 2006 3:00 pm Post subject: |
|
|
You can certainly do that and that is what I`m now gonna do, but I think acknak says your argument against having my desired function can be applied to the search & replace function, too. But everyone is okay with the search & replace function, so why not have the same search & replace function with numeric conditions?
However, for me, with OOo Base and MySQL, everything is fine. |
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
Powered by phpBB © 2001, 2005 phpBB Group
|
