MIN and MAX like an AVERAGEIFS?

[superchill435]

=AVERAGEIFS(D10:D107,M10:M107,">1")

this formula uses the cells in column D to calculate the average (but only if their corresponding cell in M is greater than 1).

using this same test logic, how do I calculate the minimum and maximum of the cells in column D (but only if their corresponding cell in M is greater than 1?

[zbor]

Try: =MAX(IF(M10:M107>1, D10:D107))
confirmed with ctrl+shift+enter (not just enter)

Same for a MIN

[Andrew-R]

Something along the lines of:

=MAX(INDEX(D10:D107*(M10:M107>1),0))

The minimum is trickier:

=MIN(INDEX(D10:D107+((M10:M107<=1)*9999999),0))

[superchill435]

zbor...urock.
just learning Excel formulas and this makes perfect sense.
what's up with the ctrl+shift+enter???

[zbor]

It's called array formula.
An array formula is a formula that works with an array, or series, of data values rather than a single data value.

To enter a formula as an array formula, type the formula in the cell and press the CTRL SHIFT and ENTER (CSE) keys at the same time rather then just ENTER. You must do this the first time you enter the formula and whenever you edit the formula later. If you do this properly, Excel will display the formula enclosed in curly braces { }. You do not type in the braces -- Excel will display them automatically. If you neglect to enter the formula with CTRL SHIFT ENTER, the formula may return a #VALUE error or return an incorrect result.

[superchill435]

thanks for all the help.
much appreciated.