Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to know location of cell found with MIN/MAX?
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to know location of cell found with MIN/MAX?
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. "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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to know location of cell found with MIN/MAX?
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) "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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to know location of cell found with MIN/MAX?
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to know location of cell found with MIN/MAX?
In .com, Pete_UK
spake thusly: 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 --------------- 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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to know location of cell found with MIN/MAX?
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*SU BTOTAL(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(B 2,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? "Dallman Ross" wrote: In .com, Pete_UK spake thusly: 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 --------------- 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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to know location of cell found with MIN/MAX?
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" wrote: 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*SU BTOTAL(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(B 2,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? "Dallman Ross" wrote: In .com, Pete_UK spake thusly: 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 --------------- 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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to know location of cell found with MIN/MAX?
In , JMB
spake thusly: 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 ---------------------------------- "JMB" wrote: 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*SU BTOTAL(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(B 2,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? "Dallman Ross" wrote: In .com, Pete_UK spake thusly: 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 --------------- 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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to know location of cell found with MIN/MAX?
Back on 10 October 2006 in
, JMB spake thusly: 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*SU BTOTAL(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(B 2,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,OFFS ET(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 ========================================== "Dallman Ross" wrote: In .com, Pete_UK spake thusly: 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 --------------- 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 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to know location of cell found with MIN/MAX?
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(B 2,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(B 2,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,R OW(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 "Dallman Ross" wrote: Back on 10 October 2006 in , JMB spake thusly: 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*SU BTOTAL(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(B 2,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,OFFS ET(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 ========================================== "Dallman Ross" wrote: In .com, Pete_UK spake thusly: 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 --------------- 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
define one cell location throughout workbook? | Excel Worksheet Functions | |||
Custom functions calculating time arguments Help Desperate | Excel Worksheet Functions | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
Reference Cell if Found in a IF function | Excel Worksheet Functions | |||
up to 7 functions? | Excel Worksheet Functions |