Using MIN Function for a set of numbers in a column above a defined value.

[Zagra147]

I have a set of numbers in a column, say A1:A50, which can vary from '0' to '5000'. I wish to find the minimum number from all values above a defined value, say '500'. All numbers below and including '500', (including '0'), to be ignored.

An obvious function would be a 'MINIF'. But this does not exist (at least in 2010 it doesn't).

Can anyone help?

Regards,

Alan.

[Bob Phillips]

An array function like this

Please Login or Register  to view this content.

[Aardigspook]

Or use Aggregate if you prefer a non-array formula:

Please Login or Register  to view this content.

[Zagra147]

I had tried using the 'IF' function (which seems to be the obvious route) as Bob suggested but all I seem to get is #Value. However, the 'Aggregate' function works fine. Thanks, Aardigspook.

I shall look into that a bit more.

(I had used the 'Aggregate' function some years ago, but at my age (83) it had somehow, er, slipped my memory...).

[Bob Phillips]

I had tried using the 'IF' function (which seems to be the obvious route) as Bob suggested but all I seem to get is #Value.

It is an array formula, so after typing it you hit Ctrl-Shift-Enter, not just Enter. If you do that correctly, Excel will show the formula in the formula bar with braces (curly brackets) around it, don't try to type these yourself, let Excel manage it.

(I had used the 'Aggregate' function some years ago, but at my age (83) it had somehow, er, slipped my memory...).

Not that many years ago, Aggregate is relatively new.

[Aardigspook]

You're welcome, glad we could help.
If you're happy that your question has been answered, then please take a moment to mark the thread as Solved so others know there's an answer here (instructions are in my sig). Thanks.

[Zagra147]

Thank you both. (I was pressing Ctrl/Alt, Bob. Silly me!)