Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / March 2007

Tip: Looking for answers? Try searching our database.

Why MIN/MAX Compulsory

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
dindigul - 31 Mar 2007 23:18 GMT
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
Dave Peterson - 31 Mar 2007 23:41 GMT
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.

> 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

Signature

Dave Peterson

JE McGimpsey - 31 Mar 2007 23:48 GMT
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
<erJ9lH#cHHA.4516@TK2MSFTNGP04.phx.gbl>,

> 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
Bob Phillips - 01 Apr 2007 00:32 GMT
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.

Signature

---
HTH

Bob

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

>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

Rate this thread:






 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.