Find row/column of value in Unsorted Array

  • Thread starter Thread starter femakid
  • Start date Start date
F

femakid

I am trying to do the following.

I have an unsorted array with values 12 columns wide, and 15 rows deep
(Fantasy Football scores, 12 owners, 15 weeks)

I can find the actual value using the large function on the UNSORTED
array. But now I want to find the owner, and week, the largest value
occurred.

A visual can be seen at http://movieraiders.com/ffb/Categories.htm

Under the records tab, weekly section I am having to manually type the
week and Manager because I can't figure out how to pull the data from
the corresponding worksheet.

Thoughts?
 
The following array formulae should work for you:

Week Number:
{=OFFSET(A1,INT(MIN(IF(B3:M16=MAX(B3:M16),ROW(B3:M16)+(COLUMN(B3:M16)/1000),
65537)))-1,0)}

Player Name:
{=OFFSET(A2,0,(MIN(IF(B3:M16=MAX(B3:M16),ROW(B3:M16)+(COLUMN(B3:M16)/1000)))
-INT(MIN(IF(B3:M16=MAX(B3:M16),ROW(B3:M16)+(COLUMN(B3:M16)/1000)))))*1000-1)
}

Just in case you don't know: You will need to press Ctrl-Shift-Enter
(instead of just Enter) after typing these formulas to make them work.

(I'm assuming your data range is B3:M16 in the above -- change them to
whatever suits you.)

/i.
 
Wow Immanuel you are absolutely the man!

That worked perfectly! (I didn't hit Ctrl-Shift-Enter and it still
worked, why would I need to hit those keys?)

I am trying to decipher exactly what you have going on in there. I
could probably figure out a few of them, but the key questions I have
(if you are willing to take the time to explain them.

What is the A1/65537, and A2/1000 references in the beginning of the
command for OFFSET?

And then I divide or multiply the result and force an integer?

Thank you very much again, that was VERY helpful :)
 
Glad to hear that it worked for you. :)

Since the formula needs to return both the row and column of the cell that
contains the max value, and since we're sorting the values to find the first
occurence, we're limited to returning one value. So, the formula returns
one number (with the row as the integer portion and the column as the
decimal portion). There are 255 columns in XL so we divide the column
portion by 1000 to allow for a max of 999 columns.

When we want to get the row of the occurence of the max cell, we can use
INT() to truncate the value. To get the column, we use INT(n-INT(n)*1000).

Does this clarify things a bit?

Just for fun, try:
=MIN(IF(B3:M16=MAX(B3:M16),ROW(B3:M16)+(COLUMN(B3:M16)/1000),65537))

You should get something like 14.007. 14 is the row and 7 is the column of
the cell in which the max is found.

As for Ctrl-Shift-Enter, I would have thought that it would be required
since IF() is being presented with an array... Specifically:

=MIN(IF( _B3:M16_ =MAX....
The IF() should look at each value in the array and compare it to the MAX of
the array. Since it would need to iterate, I would have thought it would
need to be array-entered.

Odd, but really interesting, that it works as a regular function.

The A1 reference in OFFSET is the starting point of the seek for the name.
It doesn't really have much to do with 65537... The value 65537 is more
than the maximum number of rows in XL. So if we get a value of 65537, we
know that the cell does not contain the max. And since we use the MIN
function to extract the first occurence of the max number, 65537 gets
immediately placed at the bottom of the list.

Again, glad it worked for you. If you'd like more clarification, don't
hesitate to ask.

/i.
 
Back
Top