How to know location of cell found with MIN/MAX?

[Dallman Ross]

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

 

[JMB]

Relative or absolute location?

If your data is in A7:A16
=MATCH(MAX(A7:A16),A7:A16,0) will tell you the index number of the first
item in A7:A16 that has the max.

=ADDRESS(ROW(A7)+MATCH(MAX(A7:A16),A7:A16,0)-1,COLUMN(A7))
will give you the absolute cell address.

 

[JLatham]

What you have to keep in mind is that MIN() and MAX() are just determining
relative values of the entire group without being specific as to which cell
in the group has "the" value. If there are one hundred entries with 32 in
your list, MIN() is just going to return 32. Then the MATCH() is going to
pick up the first one in the specified range to examine.

To identify all of the cells in your list that contain the MIN/MAX found,
you could put the MIN/MAX formula into another cell and set conditional
formatting for entries in your list to highlight cells in the list that have
the same value as the cell where you set up the MIN/MAX formula, or...

Use the 'Formula Is' option in Conditional Formatting to set up 2
conditions, one where the cell's contents is = to the MIN() in the list (one
highlight color) and the second condition's formula to test if it is = to the
MAX() in the list (second highlight color)

 

[Pete_UK]

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

 

[JLatham]

Good point!

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]

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.

Pete,

Yes, thanks; I left off the details of what I'm doing for simplicity's
sake, but I am, indeed, already doing it exactly as you suggest:

=SUBTOTAL(5,range)

for example. I still would like to know if, once Excel finds
such a result, there is some internal value I can access that
will tell me the location of the cell that was found. Since
the values in "range" are not necessarily unique, using,
e.g., MATCH on the same range to find the row number is not
helpful -- for the very reason you cite to do with filtered
data versus all data.

Your input was appreciated.

dman

---------------

 

[JMB]

Sorry, I apparently did not read all of your post - the part starting w/ "I
don't want to use MATCH". So if your range is filtered, you want the
location of the visible cell corresponding to the minimum value of the
visible range?

If your data is in B2:B7 and you want what I would call a relative index
number of the first visible cell equal to the minimum of the visible cells,
try

=INDEX(SUBTOTAL(2,OFFSET(B2,0,0,ROW(INDIRECT("1:"&ROWS(B2:B7))),1)),MATCH(SUBTOTAL(5,B2:B7),B2:B7*SUBTOTAL(2,OFFSET(B2,ROW(INDIRECT("1:"&ROWS(B2:B7)))-1,0)),0))

If you want the absolute index number of the first visible cell that equals
the minimum value of visible cells, try

=MATCH(SUBTOTAL(5,B2:B7),B2:B7*SUBTOTAL(2,OFFSET(B2,ROW(INDIRECT("1:"&ROWS(B2:B7)))-1,0)),0)

both are array entered (Cntrl+Shift+Enter).

You could change Subtotal(5,B2:b7) to a cell reference to make it more
usable. Is this a step in the right direction?

 

[JMB]

Forgot to include a link regarding creating an array of filtered items from a
list

http://j-walk.com/ss/excel/eee/eee001.txt

look for the power formula technique created by Laurent Longre (top of page
2).

 

[Dallman Ross]

Forgot to include a link regarding creating an array of filtered items from a
list

http://j-walk.com/ss/excel/eee/eee001.txt

look for the power formula technique created by Laurent Longre (top of page
2).

JMB,

I very much appreciate this post. I didn't answer right away, because
I needed more time to digest it. (It's complex enough to tax my brain.) ;-)
I still need more time -- but I will be testing the ideas over the next
day or two. I wanted to thank you now, lest you think I didn't notice
your post.

I'll come back to this later with reactions and perhaps more questions.

Dallman Ross

----------------------------------

 

[Dallman Ross]

Back on 10 October 2006 in

Sorry, I apparently did not read all of your post - the part starting w/ "I
don't want to use MATCH". So if your range is filtered, you want the
location of the visible cell corresponding to the minimum value of the
visible range?

Yes. Though maybe I should simplify my request: I want a way to know
a row number from the filtered data. I was using SUBTOTAL(5,...)
simply because it was a convenient way to find something in the
filtered data. But any good way to find a row in the filtered
data will do. I should also add that this is under Excel 2002
under Win XP Pro SP2.

If your data is in B2:B7 and you want what I would call a relative index
number of the first visible cell equal to the minimum of the visible cells,
try

=INDEX(SUBTOTAL(2,OFFSET(B2,0,0,ROW(INDIRECT("1:"&ROWS(B2:B7))),1)),MATCH(SUBTOTAL(5,B2:B7),B2:B7*SUBTOTAL(2,OFFSET(B2,ROW(INDIRECT("1:"&ROWS(B2:B7)))-1,0)),0))

I had been saving this post for when I had time to try. I just
did. I had to fool with it for a while to get it to work. But
it does, indeed, work! Your second formula, below, is more
helpful to me, though, because I want an absolute index number
(row number).

If you want the absolute index number of the first visible cell that equals
the minimum value of visible cells, try

=MATCH(SUBTOTAL(5,B2:B7),B2:B7*SUBTOTAL(2,OFFSET(B2,ROW(INDIRECT("1:"&ROWS(B2:B7)))-1,0)),0)

Almost. It gives a result one under the actual index. So I added
1 to the result. Here is one example that works with my data, when
I enable it as an array formula:

=MATCH(SUBTOTAL(5,X2:X673),X2:X673*SUBTOTAL(2,OFFSET(X2,ROW(INDIRECT("1:"&ROWS(X2:X673)))-1,0)),0)+1

As you can see, I have a fair bit of data. I have nearly 700
rows of data now, and the table grows daily until the end of the
calendar year. So I guess there will be 900 or so rows by year's
end. When I filter, I look at anywhere from a couple to 30-40 rows
at a time.

Okay, well, your approach does work! I'm a bit mystified and also
astonished. (Oh, and lest I forget to mention: grateful!) I am
having a hard time figuring out the logic behind your heuristic.
I'm certain there is good logic there! I just am getting lost
trying to follow all the nested commands.

both are array entered (Cntrl+Shift+Enter).

Good thing you added that tidbit. I almost gave up.

You could change Subtotal(5,B2:b7) to a cell reference to make it more
usable. Is this a step in the right direction?

Yes! But -- aside from "How does it work?" I have a couple more
questions. One, I'm up against calculation hell in this spreadsheet.
I've gone over everything for a week cleaning it up, adding helper
columns, etc., and now it's *almost* fast, so that it only sort
of gets on my nerves and it's down to a 2-second wait. (Was about
20 seconds.) But I can't have other workbooks open at the same time,
because the delay happens with them, too, and drives me nuts. So
my first question is: are these array formulas intensive for the
computer? My second question is, is there an easier way simply to
find any old visible row number ("absolute" index/row number is
what I'm after)?

Now I'll confess that I went for an easy way out meanwhile and added
a "primary key" column of unique values. (I use row numbers, but
not of this sheet; I use the row numbers of the sheet that holds
the raw data.) So I can do, e.g., SUBTOTAL(5,...) on that column
and have a surefire index of something visible, and that's fine.
Still, I'm fascinated by your approach to my problem and want
to know all the gory details. Thanks again, JMB!

Dallman

==========================================

 

[JMB]

You're welcome, but I can't take credit for Laurent's work. I'll try to
explain, though

=MATCH(SUBTOTAL(5,B2:B7),B2:B7*SUBTOTAL(2,OFFSET(B2,ROW(INDIRECT("1:"&ROWS(B2:B7)))-1,0)),0)


Starting in the middle with this
ROW(INDIRECT("1:"&ROWS(B2:B7)))

Rows(B2:B7) returns 6 so now we have
ROW(INDIRECT("1:6"))

which returns an array of numbers 1, 2, 3, 4, 5, 6. You could use just
Row(1:6) but this will be linked to rows 1:6, so if you insert a row at cell
A1, this formula changes to Row(2:7). The indirect function will refer to
rows 1:6 even if rows are inserted/deleted or cut/pasted.

The array of numbers generated by ROW as used as the row offset argument of
the OFFSET function (minus 1 because we want B2 to be the first cell
evaluated and really need an array of numbers 0 - 5). Offset returns a range
reference x number of rows and y number of columns from B2.

OFFSET(B2,ROW(INDIRECT("1:"&ROWS(B2:B7)))-1,0)

So OFFSET will return the cells B2:B7 (one at a time) to the Subtotal
function.
SUBTOTAL(2,OFFSET(B2,ROW(INDIRECT("1:"&ROWS(B2:B7)))-1,0))
Subtotal will return a 0 for cells that are hidden by a filter. By using
the count argument (2) for subtotal and evaluating the cells in B2:B7 one at
a time (which was the result of OFFSET) you get an array of 1's and 0's. 1's
being the visible cells and 0's being the hidden cells.

This array of 1's and 0's is multiplied by B2:B7.
B2:B7*SUBTOTAL(2,OFFSET(B2,ROW(INDIRECT("1:"&ROWS(B2:B7)))-1,0))
so you have an array of 0's (hidden cells) and data for your visible cells.

Then the MATCH function is used to match your criteria to this array (in
this case the minimum value of the visible cells (Subtotal(5,B2:B7)
=MATCH(SUBTOTAL(5,B2:B7),B2:B7*SUBTOTAL(2,OFFSET(B2,ROW(INDIRECT("1:"&ROWS(B2:B7)))-1,0)),0)

After stepping through it, I see that one weakness is if the minimum value
of the visible cells is 0 it won't work properly. Therefore, I should
slightly modify it. Instead of multiplying the array of 1's and 0's by B2:B7

=MATCH(SUBTOTAL(5,B2:B7),IF(SUBTOTAL(2,OFFSET(B2,ROW(INDIRECT("1:"&ROWS(B2:B7)))-1,0)),B2:B7,""),0)

Use an IF statement. If the cell is visible (Subtotal returns a 1) return
the data in B2:B7. If not (subtotal returns a 0), then return a blank "" -
which will be ignored by the match function.

As a side note, I should mention that excel stores the boolean TRUE value as
a 1 and FALSE as a 0. An IF statement will treat 0's as FALSE and numbers
that are not 0 (both positive and negative) as TRUE. So using IF to test for
zero is redundant.

=IF(B2<>0, "It's True", "It's False")
can be written
=IF(B2, "It's True", "It's False")


Yes, array formulae can add to the calculation load, particularly since this
one is using volatile functions (OFFSET and INDIRECT - at the least).
Volatile functions recalculate every time excel does even if the formulas
precedents did not change.

More on calculation speed here. Also has some links to other web sites
w/discussions on array formulae.
http://www.decisionmodels.com/optspeed.htm