[Solved] Determine MAX and MIN, with multiple criteria

[giuvi]

Hallo Community!

I need to find the max and min values from a triple column spreadsheet and I tried the function

={MAX(IF(AND(Range1=Value1;Range2="");ValueRange))}

and respective with MIN. But I get

Err:512

=> "Function too long".

Is it possible? Or there is an error in my input?

Edit: Changed subject, was MAX IF: "AND" supported?  Make your post understandable by others  -- MrProgrammer, forum moderator

[Alex1]

If I assume Range1, Range2 and ValueRange are names defined as columns and Value1 as a cell, I don't get that error message.

[FJCC]

A formula like the following, entered as a array formula, works for me if the maximum value is positive.

Code: Select all

=MAX((A1:A8="A") * (B1:B8="") *(C1:C8))

[giuvi]

@FJCC, many thanks for the quick answer!

The MAX function runs very well - checked with SUBTOTAL function in filtered spreadsheet. But the MIN function doesn't work! I always get the value 0, ans this is not possible, as also checked.

[FJCC]

Yes, the MIN() function won't work. The formula relies on the fact that TRUE comparisons have a value of 1. When A1:A8 = "A" is true, it gets the value 1 and the same goes for B1:B8 = "". You end up with 1 * 1 * C1:C8 for the rows where both the test conditions are TRUE. A FALSE comparison has a value of zero, so all your rows where either test condition is FALSE result in the multiplication being equal to zero. So, the MIN() of the multiplications is always zero.
I can't think of a way to get the MIN() version to work. A pivot table would allow you to find the result easily enough if you are willing to use that tool.

[MrProgrammer]

I need to find the max and min values from a triple column spreadsheet

You asked a very similar question six years ago: [Solved] Largest If?

After reviewing that if you still need help I think I can show you a method for MIN, perhaps several methods. Attach a spreadsheet demonstrating the situation (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the spreadsheet itself). I will not help further unless you attach a spreadsheet. If the real data is confidential and you have to supply fake data, make sure it accurately illustrates the real data. Otherwise the solution you receive may not apply to the real problem. For example, if the real data has negative numbers, the fake data should have them as well.

Function too long

I am unable to reproduce that problem.

[giuvi]

You asked a very similar question six years ago: [Solved] Largest If?

1. Yes, I asked something similar about 6 years ago. Yesterday, before asking here in the forum, I searched in my old calc files, where I supposed, the formula was used. But I didn't found it. And indeed (!) in my search in this forum I haven't thought to look up my old threads.
SORRY! Shame on me!

2. Yes, the old thread, you have mentioned, contains the right "MAX(IF(" array formula for me.

After reviewing that if you still need help I think I can show you a method for MIN, perhaps several methods.

3. Only to clarify: My current thread is similar to the old one, but it tends to solve another question on another calc table on a similar calc file.

Attach a spreadsheet demonstrating the situation (...) not Quick Reply, (...) I will not help further unless you attach a spreadsheet. (...) Otherwise the solution you receive may not apply to the real problem. For example, if the real data has negative numbers, the fake data should have them as well.

4. About a possible solution for a conditional MIN function:
a) I have today realised, a MIN array formula with only one condition is enough for my purpose. I set:

Code: Select all

{=MIN(IF(CellRange="A";Value-CellRange))}

b) however I find useful - not only for me - to know, which MIN functions support more then one condition.
That means, I will prepare the related spreadsheet (obviously with real data).
Please concede me some time, because the table should perform without links to other spreadsheets.
I think, I can do that until tomorrow evening (CET) .

BTW 1: The prepared spreadsheet will contain neither negative values nor no fake data.
BTW 2: I don't need at all a quick solution!
So long!!

[giuvi]

(...) Attach a spreadsheet[/url] demonstrating the situation (remove confidential information

I've forgotten to ask:
a) I think, the demonstration spreadsheets should have English formulas. Is it true?
b) Should I provide them with English labelled columns?

Thanks in advance for response

[FJCC]

a) The formulas will automatically be displayed in the the language of the user's OpenOffice locale. There is no need to translate them.
b) Column headers in other languages are not usually a problem but it might make the document easier to understand if they were in English.

The most helpful things are to have the simplest document possible that demonstrates the problem without needless formatting or irrelevant data and to have a clear explanation of the desired result. For example, "I need F1 to show the minimum value in column D under the conditions that column A contains "bob" and column H is blank".

[giuvi]

Hello!

At first a good news - there is no bad news!
While preparing the test file, I finally learnt how to use the MIN(IF())) array function with more then one condition, as indeed already suggested by MrProgram in viewtopic.php?t=93608

However I'm curious to know other formulas to get the same. So I have uploaded the test file for simpler explanation.

Arb-Std. 2024 TEST.ods

test AOOcalc file

(95.85 KiB) Downloaded 20 times

Test file description
it contains two tabs:
1. In Year Report there is the data to be evaluated
2. in Plausibility Check there is the table with the desired statistical values

The Year Report
This shows which tasks I have absolved and how much time I needed for. It is used as monthly report to my employer (only columns E -> I) as PDF file.
a) In row 11 are written the range names for the respective columns, for each one the rows 17->808;
b) The col. Job 1 lists the name of the principal task, sometimes of the second one in col. Job 2.
c) The column I shows the job names, often the first stop time, sometimes other information (not important here). All this data are now only as text, but they were originally got from other columns or rather other spreadsheets.
d) There are many hidden columns They are not important here, but I didn't eliminated them, because I am afraid, any formulas might lose references.

Plausibility Check table
I'd like to use it in order to reduce typing errors on input time data, just a plausibility check table. So I can manually check the report for the usual tasks quicker, before sending it. .
a) The col. "LS Job" shows the usual tasks (a sort of milk round) for which I need the duration values.
b) The values in the right part table should shows statistical values for the respective jobs inside given data range (between D4 and F4).

What I need in the Plausibility Check table:
As already written above, I found the solution for my case. But I find interesting, if there are other formulas to calculate:
a) the minimum time value in given data range;
b) the minimum time value in given data range for the respective jobs done on Monday, when every one need much more time for job explication.

I hope, I've given all necessary information about the test file and thank you very much for support.

[MrProgrammer]

However I'm curious to know other formulas to get the same.

Given the layout of that data some of the methods I have used in other situations are not applicable. You are using three cell ranges as the source for your filter:
• 'Plausibility Check'.D4:F4
• 'Plausibility Check'.B7:B11
• 'Year Report'.B16:AN808

Using three ranges eliminates the simplest solution when the data is all in a single range. In the single range case, one uses a new column with IF tests for the criteria which returns the JahresStd value when the criteria are all true and returns an empty string when any of the criteria is false. A MIN of the new column is the desired value, since MIN ignores all text values (the empty strings). No array formulas are needed.

Using three ranges also eliminates using DMIN, since that function can only analyze a single range. DMIN also has the problem that empty cells, your JListeAuftragZ="" test, are ignored for its criteria tests. One has to create a new column with an ISBLANK formula for JListeAuftragZ and use a NewColumn=1 criteria. The same criteria difficulty would affect MINIFS in LibreOffice. No array formulas would be needed if you could use DMIN or MINIFS.
[Solved] Min Number in range greater than 0
[Solved] Search criteria for database functions
[Solved] Last value of a column that matches a condition
[Solved] Get 2nd smallest distinct value in list

A pivot table is often a good way to get min/max/average summaries, but the need to calculate second and third maxima, and the selection of a specific subset of JListeAuftragA values, prevents using that approach. Thus you need to use an array formula. These are useful but have many quirks so I prefer to avoid them if there are other solutions.

I would simplify H7 to =MIN(IF((JListeAuftragA=$B7)*(JListeAuftragZ="")*(JListeDatum>=$D$4)*(JahresStd<=$F$4)*(WeekDayDate=1);JahresStd;"")), replacing "check" by "". Either value is ignored by MIN. You can also use nested IF functions like =MIN(IF(JListeAuftragA=$B7;IF(JListeAuftragZ="";IF(JListeDatum>=$D$4;IF(JahresStd<=$F$4;IF(WeekDayDate=1;JahresStd;"");"");"");"");"")). Nested IF is longer and the extra parentheses are messy, but the idea is clearer perhaps.

You cannot use AND in your array formula because AND would need to return an array of values to IF, and then to MIN, however AND always returns a single value. The single value from AND will always be FALSE unless the test is true for every row. When AND always returns FALSE, the value from JahresStd is never selected and MIN returns 0. That's why either multiplication or nested IF is needed for array context.

For F7 you can remove the redundant less than test of column D and use =MAX((JListeAuftragA=$B7)*(JListeAuftragZ="")*(JListeDatum>=$D$4)*(JListeDatum<=$F$4)*(JahresStd<$D7)*(JahresStd<$E7)*JahresStd).

If this solved your problem please go to your first post use the Edit ☐ button and add [Solved] to the start of the Subject field. Select the green checkmark icon at the same time.

[giuvi]

@MrProgrammer,

many thanks for suggestions!

A pivot table is often a good way to get min/max/average summaries, but the need to calculate second and third maxima, and the selection of a specific subset of JListeAuftragA values, prevents using that approach. Thus you need to use an array formula. These are useful but have many quirks so I prefer to avoid them if there are other solutions.

In the past I've often used pivot tables, mostly to analyse data, sometime to catch values for another table. But - as you have mentioned - it is not useful in this case. Furthermore I find more "elegant" using array formulas, if possible. Maybe they also need less calculate resources than a pivot table.

About your "corrections" to the functions I used:

=MIN(IF((JListeAuftragA=$B7)*(JListeAuftragZ="")*(JListeDatum>=$D$4)*(JahresStd<=$F$4)*(WeekDayDate=1);JahresStd;"")), replacing "check" by "".

I added "check" at the and, because I want to get a specific return message, if the formula is correct, but no value is found. Obviously I'll eliminate that, as MIN does not support the Either value.

For F7 you can remove the redundant less than test of column D and use =MAX((JListeAuftragA=$B7)*(JListeAuftragZ="")*(JListeDatum>=$D$4)*(JListeDatum<=$F$4)*(JahresStd<$D7)*(JahresStd<$E7)*JahresStd).

Sure! In the hurry I used copy 'n past, then added the new condition instead to change (or eliminate) the redundant one.

You cannot use AND in an array formula because AND would need to return an array of values to IF and then to MIN, however AND always returns a single value. That's why either multiplication or nested IF is needed for array context.

Now I have understood the AND trouble in array formulas:instead multiplication factors give a row (in the matrix) as result.

Now I think, we can consider the thread as solved, can't we?

I am grateful for your suggestions and explanations!