interpretation of formula to rank visible rows

[nevergiveup]

Back in 2006 Domenic posted reply below for how to rank visible cells when using excel's auto-filter. I am hoping that someone can break this down in English as to how (maybe why) it works. I just can't follow the steps inherent in this formula.

Please help interpret


Here's the quote from Domenic answering the question:

how can i use the rank filter to filter only rows selected by an auto filter?

Assuming that A6:F19 contains the data, try...

G6, copied down:

=SUMPRODUCT(SUBTOTAL(3,OFFSET($F$6:$F$19,ROW($F$6:$F$19)-ROW($F$6),0,1)),--(F6<$F$6:$F$19))+1

Hope this helps!

 

[PaddyD]

Hi.

The key to the technique is the subtotal(3...) component. if you review teh ecel help file for subtotal, you'll find that (a) subtotal "...ignores any rows that are not included in the result of a filter" and (b) 3 is the function_num reference for counta().

So you can get the idea that the subtotal(3...) is a technique to identify visible rows. The rest of the formula constructs a usable array from these identifications, then constructs a ranking.

Need it more explicit?

 

[nevergiveup]

Thanks, PaddyD. The subtotal is actually the piece I get. I also have some understanding of the offset function but I use only cell references rather than arrays with it. And, believe it or not, I do know something, though not tons, about working with arrays in excel.

Still, this seems one or two steps beyond me. For example:

1. how does the offset work with arrays
2. what does the height and width piece of the offset function do here
3. and what the heck is the -- near the end of the formula.

So, yes, my brain needs a very explicit interpretation.

Thanks again!

 

[PaddyD]

OK. Too busy to get into the details right now. I'll check back later & write something up step-by-step if no-one else has popped by.

 

[nevergiveup]

Sounds great. Thanks.