"balmalik" skrev i
en meddelelse ...

Thanks for the reply...

Can I ask one more question? Can I specify a specific range? or a
maximum acceptable value? Thanks.


--
balmalik


You're welcome.

For a specific range try this setup:
"Data" is the name of the range, which could
be D2:H100.

In e.g. J2 insert this formula:

=LARGE(Data,ROW()-ROW($J$2)+1)

$J$2 acts as a counter, so in case you start
in another cell, e.g. L6, $J$2 must be replaced
by $L$6.

Copy down to e.g. J3:J7. You now have the
6 greatest values in "Data" (including duplicates)

In K2 insert this array formula:

=ADDRESS(MAX((Data=J2)*ROW(Data)),MATCH(J2,OFFSET( Data,MAX((Data=J2)*
ROW(Data))-MIN(ROW(Data)),0,1),0)+MIN(COLUMN(Data))-1,4)

entered as one line. Finish with <Shift<Ctrl<Enter, also
if editing the formula later.

Copy K2 to K3:K7, and you have the addresses.

In case of duplicates the same address is displayed.
The address is the address of the last found duplicate
in "Data".

For minimum values use this setup:

In N2:

=SMALL(Data,ROW()-ROW($N$2)+1)


In O2:

=ADDRESS(MIN(IF((Data=N2)*(ROW(Data))<0,(Data=N2) *(ROW(Data)))),
MATCH(N2,OFFSET(Data,MIN(IF((Data=N2)*(ROW(Data))< 0,(Data=N2)*
(ROW(Data))))-MIN(ROW(Data)),0,1),0)+MIN(COLUMN(Data))-1,4)

Again as one line and finished with <Shift<Ctrl<Enter.
Duplicates as described above.


I don't understand your question about a maximum acceptable
value.

---
Regards
Leo Heuser