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.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 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.=MIN(IF((JListeAuftragA=$B7)*(JListeAuftragZ="")*(JListeDatum>=$D$4)*(JahresStd<=$F$4)*(WeekDayDate=1);JahresStd;"")), replacing "check" by "".
Sure! In the hurry I used copy 'n past, then added the new condition instead to change (or eliminate) the redundant one.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).
Now I have understood the AND trouble in array formulas:instead multiplication factors give a row (in the matrix) as result.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.
Statistics: Posted by giuvi — Wed Mar 27, 2024 11:37 pm
Statistics: Posted by Hagar Delest — Wed Mar 27, 2024 11:34 pm
Statistics: Posted by Frost — Wed Mar 27, 2024 11:11 pm
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:However I'm curious to know other formulas to get the same.
Statistics: Posted by MrProgrammer — Wed Mar 27, 2024 10:20 pm
Statistics: Posted by giuvi — Wed Mar 27, 2024 6:10 pm
Statistics: Posted by FJCC — Tue Mar 26, 2024 3:33 pm
Statistics: Posted by giuvi — Tue Mar 26, 2024 2:26 pm
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.After reviewing that if you still need help I think I can show you a method for MIN, perhaps several methods.
4. About a possible solution for a conditional MIN function: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.
Code:
{=MIN(IF(CellRange="A";Value-CellRange))}
Statistics: Posted by giuvi — Tue Mar 26, 2024 11:24 am