Cell reference of values
 

Hi all,
I know there's a way to find the maximum and minimum values in a range of
cells, but is there a way to return the cell references those values occupy
instead of the actual values, and ignoring zeros/blank cells in the minimum?
Thanks!

Cell reference of values
 

=ADDRESS(MATCH(MAX(F4:F16),F4:F16,0)+ROW(F4)-1,COLUMN(F4:F16))

and

=ADDRESS(MATCH(MIN(IF(F4:F16<0,F4:F16)),F4:F16,0) +ROW(F4)-1,COLUMN(F4:F16))

the second formula is an array formula, it should be committed with
Ctrl-Shift-Enter, not just Enter.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"jezzica85" wrote in message
...
Hi all,
I know there's a way to find the maximum and minimum values in a range of
cells, but is there a way to return the cell references those values
occupy
instead of the actual values, and ignoring zeros/blank cells in the
minimum?
Thanks!

Cell reference of values
 

Thanks Bob, but those formulas didn't work for me for some reason. I have
data in three columns that I want to find the cumulative max and min for
(B2:D33) and return those addresses, could that be why?

"Bob Phillips" wrote:

=ADDRESS(MATCH(MAX(F4:F16),F4:F16,0)+ROW(F4)-1,COLUMN(F4:F16))

and

=ADDRESS(MATCH(MIN(IF(F4:F16<0,F4:F16)),F4:F16,0) +ROW(F4)-1,COLUMN(F4:F16))

the second formula is an array formula, it should be committed with
Ctrl-Shift-Enter, not just Enter.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"jezzica85" wrote in message
...
Hi all,
I know there's a way to find the maximum and minimum values in a range of
cells, but is there a way to return the cell references those values
occupy
instead of the actual values, and ignoring zeros/blank cells in the
minimum?
Thanks!

Cell reference of values
 

OK, first the formula that is working:

Placing, say, =MAX(B2:D33) in $H$7, the following formula will return
the address of the maximum element:

=ADDRESS(ROW(B2)+INT((SUMPRODUCT(--(N(OFFSET(B2,INT((ROW(1:96)-1)/3),MOD((ROW(1:96)-1),3)))=$H$7),ROW(1:96))-1)/3),COLUMN(B2)+MOD(SUMPRODUCT(--(N(OFFSET(B2,INT((ROW(1:96)-1)/3),MOD((ROW(1:96)-1),3)))=$H$7),ROW(1:96))-1,3))

It is semiparametric.
- Replace B2 with the first cell of your values.
- Replace 3 with the number of columns.
- Replace 96 with the number of total data (he 3*32)

Note: this is a rather imperfect formula. It will not work if more than
one elements in the data have the MAX value. But it has been tested
against a table of random data with the same dimensions, with values
guaranteed to be inside the data.


Bob, are you still reading this thread? My first approach was to find
the row in which MAX is appearing and similarly the column in which MAX
is appearing and use the two values in the Row and Column args of
ADDRESS. Yet, the first attempt with OFFSET was disappointing (half
expectedly, but I don't understand why yet). The "equivalent" formula
using INDIRECT instead,

=SUMPRODUCT(--ISNUMBER(MATCH($H$7,N(INDIRECT("b"&ROW(2:33)&":d"& ROW(2:33))),0))*ROW(1:32))

produces 0, which it should not. Which dark aspect of ref returning
functions have we reached here?

Regards,

Kostis