| View previous topic :: View next topic |
| Author |
Message |
digiproc
Newbie
Joined: 30 Jan 2011
Posts: 3
|
 Posted: Sun Jan 30, 2011 6:44 am Post subject: [Solved] greatest min in one col for all same in other col |
 |
|
Sorry for the convoluted title! The gist is that I have a somewhat complicated task (for me the noob). I'm a programmer so I know the algorithm is fairly simple, but I am BRAND NEW to Calc and would rather learn how to do this in Calc than export the sheet and process it with a Python or C++ program.
So here is the problem:
For each set of rows that have the same value in Col A, I need to get the minimum Col B value.
For example:
| Code: |
Col A Col B
0 -.4
0 .2
0 1.1
0 -1.3
1 .065
1 -.03
1 2.4
1 1.03
2 -.944
2 -.69
2 3.5
2 1.2
|
So for the above data I would get:
| Code: |
Col A value Min Col B value
0 -1.3
1 -.03
2 -.944
|
Additionally, I don't necessarily need the result data created. My end goal is simply to find the Col A value that has the greatest minimum Col B value. But given the second sheet above I know finding the maximum "Minimum Col B value" is trivial. For example I can simply sort that sheet.
On a side note, I have the same number of rows for each value of Col A (for example, above I show 4 rows for each possible value in Col A). Don't know if this simplifies the task or not.
I have thousands of values for Col A. Can someone show me how to do this without typing thousands of different formulas?
Last edited by digiproc on Mon Jan 31, 2011 9:31 am; edited 1 time in total |
|
| Back to top |
|
 |
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
|
| Back to top |
|
|
digiproc
Newbie
Joined: 30 Jan 2011
Posts: 3
|
| Posted: Sun Jan 30, 2011 12:42 pm Post subject: |
|
|
| Villeroy wrote: | http://wiki.services.openoffice.org/wiki/Documentation/OOo3_User_Guides/Calc_Guide/DataPilot
In a database it would be as easy as
| Code: | SELECT "Field 1", MIN("Field 2")AS "Minumum"
FROM "Some Table"
GROUP BY "Field 1"
ORDER BY MIN("Field 2") DESC |
|
Thanks! looks like that is the way to go. In the mean time I went ahead and wrote a python script to do it, but eventually I want to become familiar enough with Calc and (now that I know it exists) DataPilot. |
|
| Back to top |
|
|
Sliderule
Super User
Joined: 29 May 2004
Posts: 2474
Location: 3rd Rock From The Sun
|
| Posted: Sun Jan 30, 2011 1:07 pm Post subject: |
|
|
There is a second technique to use ( built-in ) completely in the Spreadsheet.
Using DataPilot .
- Select your data ( column A and Column B down to the
bottom ( be sure to include the TITLE ROW . . . that is, for example . . . Row 1 with a 'text description' of the column )
- From the Menu: Data -> DataPilot -> Start... -> Current selection
- Press OK
- Drag the Column A description button to Row fields area
- Drag the Column B description button to the Data Fields area
- Double Click on the newly placed button in the Data Fields area and select Min
- Press the More button to display additional options . . . you can determine, for example, where to display the results, or, if you want Total rows / columns also calculated
- Press the OK button
- Smile and say: "Gee Sliderule, that was easy. Now all I have to do is mark this forum entry as [Solved] for others."
I would recommend reading the Calc Help file on the: DataPilot function;calling up and applying.
Also, to see an example WITH GRAPHICS . . . look at the post below and the Graphics included in Sliderule's ( your truly ) post:
http://www.oooforum.org/forum/viewtopic.phtml?t=103632
I hope this helps, please be sure to let me / us know.
Sliderule
Thanks to add [Solved] in your first post Title ( edit button ) if your issue has been fixed / resolved. |
|
| Back to top |
|
|
Robert Tucker
Moderator
Joined: 16 Aug 2004
Posts: 3367
Location: Manchester UK
|
| Posted: Mon Jan 31, 2011 7:03 am Post subject: |
|
|
I think this array formula also works:
| Code: | | =MAX(MIN(IF($A$2:$A$13=0;$B$2:$B$13;0));MIN(IF($A$2:$A$13=1;$B$2:$B$13;0));MIN(IF($A$2:$A$13=2;$B$2:$B$13;0))) Ctrl+Shift+Enter |
_________________
LibreOffice 3.6.6 on Fedora 18, LibreOffice 4.0.2 on Ubuntu 13.04 (Double Boot) |
|
| Back to top |
|
|
digiproc
Newbie
Joined: 30 Jan 2011
Posts: 3
|
| Posted: Mon Jan 31, 2011 9:28 am Post subject: |
|
|
| Thanks for all the good answers! Now I have some directions to go in learning the more powerful features of Calc. |
|
| Back to top |
|
|
|
