[Solved] MINIF and MAXIF Formulas

Discuss the spreadsheet application

[Solved] MINIF and MAXIF Formulas

Postby ~~puzzled~~ » Fri May 07, 2021 4:25 pm

Have very basic understanding of constructing OpenCalc formulas and would very much appreciate some help. Have successfully used Formulas for AVERAGEIF, COUNTIF, etc, but need two examples to work from to gain MINIF and MAXIF. Any help you provide will potentially assist in seeing more of my State's newspapers digitised online.

I'm trying to find the Minimum and Maximum values in a column on a separate worksheet where a text value is present in another column's corresponding cell (on that same worksheet) and is greater than 0. For instance I have one column with the electoral regions of Victoria (Australia) (i.e. "Eastern Metropolitan", "Eastern Victoria", "Southern Metropolitan" etc.). The data I'm trying to extract from is in individual columns depending on what Newspaper Directory it appeared in.

Looking at Help there appears to only be a MIN function not a MINIF or MINIFS function, as is possible with AVERAGEIFS, COUNTIFS, etc or is available in Excel. This means I will presumably have to construct an IF statement that incorporates MIN to achieve my desired outcome. From memory I read somewhere I have to hold CTRL, SHIFT and another key before activating the formula to convert to a special kind of formula. I've succesfully constructed a formula that returns the MIN or MAX in the entire cell, but not specifically the MIN or MAX for that electoral region (as defined by entry in column D).

The cell range with the electoral regions specified is "Sheet.1.D2:Sheet1.D1401". An electoral region listed in the D range is "Eastern Victoria". One of the columns to extract the MIN or MAX value from is "Sheet1.AC2:Sheet1.AC1401". I want the Minimum value greater than 0, this will then return me the result of the MINIMUM "entered" pagination for that corresponding directory.

Any help you can provide in solving this will be appreciated by myself and users of digitised newspapers. Thanks for any help you provide.
Last edited by MrProgrammer on Fri May 14, 2021 5:51 pm, edited 1 time in total.
Reason: Tagged ✓ [Solved]
OpenOffice 4.1 on Windows 10
~~puzzled~~
 
Posts: 1
Joined: Fri May 07, 2021 3:39 pm

Re: MIN and MAX IF Formulas for Newspaper Pagination

Postby Villeroy » Fri May 07, 2021 8:15 pm

LibreOffice Calc has MAXIFS and MINIFS.

https://www.libreoffice.org/discover/li ... penoffice/
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
 
Posts: 29726
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: MIN and MAX IF Formulas for Newspaper Pagination

Postby MrProgrammer » Fri May 07, 2021 9:22 pm

Hi, and welcome to the forum.

~~puzzled~~ wrote:I'm trying to find the Minimum and Maximum values in a column on a separate worksheet where a text value is present in another column's corresponding cell (on that same worksheet) and is greater than 0.
For assistance with solutions for OpenOffice attach a document demonstrating the situation (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the document itself). Specify the cell which contains the value to match in column D and specify the cell which is to display this minimum value.

[Tutorial] Ten concepts that every Calc user should know
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 4006
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA


Return to Calc

Who is online

Users browsing this forum: No registered users and 21 guests