Why MIN/MAX Compulsory

  • Thread starter Thread starter dindigul
  • Start date Start date
D

dindigul

I have an array K1:K5 wherein 35 is one value. When I type:
{=IF(35=K1:K5, ROW(K1:K5), "")}
I neither get any reply nor the ROW number. But when include either MIN/MAX
in front of the IF statement I get the result. Why is it compulsory ? Any
ideas?
Thanks
 
I put 35 in K2 and left the others blank.

Then I selected the cell with the array formula in it and hit F2 followed by
F9. In the formula bar, I saw:
={"";2;"";"";""}

So your formula is returning an array of values. But excel can't show that
array in a single cell--it only shows the first value. In my case, it's "".

If I put 35 in K1, and did the same thing, I'd see:
={1;2;"";"";""}

and the cell would display 1.

The min/max picks out the smallest or largest value out of that array and shows
it to you.
 
When you array enter your formula, the

35=K1:K5

part returns an array of booleans, e.g., assume K4=35:

{FALSE, FALSE, FALSE, TRUE, FALSE}

So the IF function also returns an array - the row number if the
conditional is true, the null string if not:

{"", "", "", 4, ""}

If you have the array formula entered into, say, M1:M5, you'll see 4 in
M4. If you have the formula entered into only 1 cell, then you'll only
see the first result.

Wrapping the function with MAX evaluates the array, and returns the
highest row number in the array.


If you have the formula entered into only one cell, then only the first
element will be returnedIn article
 
It is not so much that MIN/MAX is compulsory, but rather that you need some
way to extract a value from a (possible) array of valid values returned from
the formula.

If you had selected 5 cells in contiguous rows, and then entered
=IF(35=K1:K5,ROW(K1:K35),""), and array-enter that formula, you will see all
of the matching row numbers returned, so it isn't compulsory.

As always, it depends upon what you want to do. If you want the row number
of the first match, use MIN, if you want the row number of the last match,
use MAX, if you want them all use it as I show.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Back
Top