[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

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
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10065
Location: Germany

Posted: Sun Jan 30, 2011 9:22 am    Post subject:

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

_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org
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.
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.
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)
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.
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
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