Finding the minimum in a range while checking for conditions to exclude cells in the range

[Banino1234]

I am having the following problem which I would like to solve using only excel functions without resorting to programming in vba or macros or manual commands.

I have a list of numbers in an array and I am looking for the minimal number in that array that satisfies some conditions, the conditions are such that they might exclude some cells in the array.
The ideal solution would have been a command like MINIF(range,condition) but such a function does not exist for MIN (it exists for counting COUNTIF)

How do I achieve the same result using other EXCEL functions?

Keep in mind that the array length and the conditions change dynamically during the run.

[Søren Larsen]

You can do it using an array formula =MIN(IF(condition1*condition2)) and finish with Ctrl-Shift-Enter

[daddylonglegs]

You wouldn't normally use AND as that can't return an array

You could get the minimum value in column A when column B is "x" and column C is > 100 like this with multiple IFs

=MIN(IF(B2:B100="x",IF(C2:C100>100,A2:A100)))

......or use * to replicate AND like

=MIN(IF((B2:B100="x")*(C2:C100>100),A2:A100))

both formulas need to confirmed with CTRL+SHIFT+ENTER

the first one above is probably marginally more efficient. You can add extra conditions as required

[Banino1234]

You wouldn't normally use AND as that can't return an array

You could get the minimum value in column A when column B is "x" and column C is > 100 like this with multiple IFs

=MIN(IF(B2:B100="x",IF(C2:C100>100,A2:A100)))

......or use * to replicate AND like

=MIN(IF((B2:B100="x")*(C2:C100>100),A2:A100))

both formulas need to confirmed with CTRL+SHIFT+ENTER

the first one above is probably marginally more efficient. You can add extra conditions as required

Many thanks, I should have provided more details:

I am having the following problem which I would like to solve using only excel functions without resorting to programming in vba or macros or manual commands.

I have a list of numbers in an array (a1:a700) and I am looking for the minimal number in that array that satisfies some conditions, the conditions are such that they might exclude some cells in the array. Cells in the array are excluded if their values are higher than a value stored in cell D1.
The ideal solution would have been a function like MINIF(range,condition) but such a function does not exist for MIN (it exists for counting COUNTIF, SUMIF, ...)

How do I achieve the same result using other EXCEL functions?

Keep in mind that the array length and the conditions change dynamically during the run.

[Søren Larsen]

I'm not sure I understand your problem, since finding the smallest value while exluding values above a given threshold, will return the same value as just finding the minimum of the array. Could you describe your problem a little more?