How to know location of cell found with MIN/MAX?
Good point!
"Pete_UK" wrote:
If you use MIN and MAX and the data is filtered, you won't necessarily
see the minimum or maximum value in the visible cells, as these
functions will apply to the entire range - use SUBTOTAL(4,range) and
SUBTOTAL(5,range) for MAX & MIN respectively, and these will apply only
to the visible cells.
Hope this helps.
Pete
Dallman Ross wrote:
I'm wondering if it's possible (and easy) to query a result
to find out the cell's location when using MIN or MAX.
E.g.:
A
---
1 120
2 343
3 32
4 934
5 36
6 32
7 661
I believe MIN will find A6, even though A3 is also the same minimum
value. Hmm, actually I'm not sure about that. But anyway, the value
found may not be unique. I'd like to know one of them in the range;
I don't really care which. E.g., A3 or A6. Can I surround the MIN
statement with some other formula that will tell me the (an)
address of the result?
I don't want to use MATCH, because, for one, this is on filtered
data, yet if my value (such as 32) appears in the unfiltered part
of the table I'll have a false answer. Or is there a way to
combine MATCH and filtered data that I'm not seeing?
Dallman Ross
|