Min/Max aus Daten mit Kriterien

List subset selection

Minimum and Maximum of selected list subset

Using multiplication to select

Intermediate step: select by multiplication

Elimination by shifting out zeros

Shifting out zero (unselected) values

Minimum und Maximum aus einer Daten- und einer Kriterienspalte

What about?

Sometimes you find yourself with a column of values and a criteria column containing 1 or 0 depending whether the value is to be included or not:

Zwei Spalten mit Kriterium und Werten

For getting set of data responding to the criteria the product of the two columns is suitable, if 0 is not a valid value:

Drei Spalten Kriterium, Wert und deren Produkt

While the Max-Function on the product column (as Matrix formula) simply returns the maximum (13) asked for, the Min-function fails because it also considers the zeros.

What's the answer?

Zeros in the product column remain zero, no matter what we do to the values. An idea is to shift down the values enough so the minimum becomes negative. An excellent choice is the maximum (determined before):

Werte- und Produktspalte verschoben

Das Minimum of these shifted products (-4) corrected for the shift (+13) is then the minimum (9) asked for.

 

Comments and Responses

×

Name is required!

Enter valid name

Valid email is required!

Enter valid email address

Comment is required!

Comments :

  • user
    ugfFKfzBgBF 21/11/2024 At 01:00
  • user
    EyFUIFycrpY 15/11/2024 At 07:49
  • user
    gdtKudNrbAVI 09/11/2024 At 14:23
Top