Conditional Min of values in an array based on a value in D1

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:a70) 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.

I have a list of numbers in an array (a1:a70).

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.

In my particular case 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 an EXCEL 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.


Thanks for your help

Try something like this...

=MIN(IF(A1:A70<D1,A1:A70))

This is an array formula. When you enter in the formula, use Ctrl+Shift+Enter. Excel will automatically surround the formula with {braces}.


Though I'm at a loss understanding your criteria. If the min value in A1:A70 is less than D1, then any value greater than D1 cannot be the minimum. So this would do the same thing for your criteria.

=IF(MIN(A1:A70)<D1,MIN(A1:A70),0)