Cell reference of values

[jezzica85]

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!

[Bob Phillips]

=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" <[email protected]> wrote in message
news:[email protected]...
> 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!

[jezzica85]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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!
>
>
>

[vezerid]

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 (here: 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