Nested formula - seeking explaination

M

mldancing

Can anyone help to explain how this formula works?

INDEX($A$2:$A$11,SMALL(IF($A$2:$A$11<>"
",ROW($A$2:$A$11),1000),ROW()-ROW($B$2)+1)-ROW($B$2)+1)

Thank you very much.
 
B

Bob Umlas

The part IF(A2:A11<>""... returns a series of true/false values, where it's
not blank, the IF will return the row of the corresponding values, like
{2;5;9} if A2, A5, and A9 are not blank. But if it IS blank, it will return
1000. So, this will be {2;1000;1000;5;1000;1000;1000;9;1000;1000}.the 2nd
part of the SMALL formula will return 1 when entered in row 2 (row() is 2,
row(b2) is 2, so this is 2-2+1; it will be 2 when filled down to row 3:
row() is 3, row(b2) is 2, so this is 3-2+1, or 2, and so on. So the SMALL
part is now =SMALL({2;1000;1000;5;1000;1000;1000;9;1000;1000},1) in the
first entry, =SMALL({2;1000;1000;5;1000;1000;1000;9;1000;1000},2) in the
2nd, etc. the SMALL(...,1) with this example is 2; the SMALL(...,2) in this
example is 5, then 9...
Now we're left with =INDEX(A2:A11,2-row(b2)+1) or INDEX(A2:A11,2-2+1) or
INDEX(A2:A11,1) or A2.
Etc.
HTH
Bob Umlas
Excel MVP
 
M

mldancing

Thanks, Bob.

Is it possible that you illustrate this formula with a simple example?
 
Top