Array Formula

[Excelnoobisme]

Hi, i've come across a array formula which i have problem understanding..
My spreadsheet look like this...

Machine #
1
1
1
2
2
2
3
3
3


formula as follow which return 8,
{=SMALL(IF(A2:A9=I6,ROW(A2:A9)-ROW(A2)+1),2)}

i understand the 'small formula' but i cant understand the 'if' formula here.
Can some1 please elaborate for me. Thanks

 

[Matty]

Hi,

That formula will return the position (in the range) of the second occurrence of whatever exists in Cell I6. So if 2 resides in I6, 5 would be returned (the second 2 resides in the 5th Cell in the range).

Matty

 

[Yahya]

your formula
=SMALL(IF(A2:A10=I6,ROW(A2:A10)-ROW(A2)+1),2)
return the position of second smallest value with condition where value in range A2:A10 Equal to the value in cell I6 in you example is 3
if you evaluate your formula
1. check if your range equal to 3
=SMALL(IF({FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE},ROW(A2:A10)-ROW(A2)+1),2)
2. then build a sequence of number equal to your range size
=SMALL(IF({FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE},{1;2;3;4;5;6;7;8;9}),2)
3.the IF formula replace true's value with the Opposite value {1;2;3;4;5;6;7;8;9}
4. your formula will look like this
=SMALL({FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;7;8;9},2)
5. as you know SMALL function will return the second smallest value which is 8

HTH

 

[Excelnoobisme]

Thanks, my I6=3, but why is it that after the '-row(A2)+1', the answer is still 8? not 6?(8-6)

 

[Excelnoobisme]

i mean why not 5, coz row(A2)+1=3 and 8-3=5?

 

[T. Valko]

Hi, i've come across a array formula which i have problem understanding..
My spreadsheet look like this...

Machine #
1
1
1
2
2
2
3
3
3


formula as follow which return 8,
{=SMALL(IF(A2:A9=I6,ROW(A2:A9)-ROW(A2)+1),2)}

i understand the 'small formula' but i cant understand the 'if' formula here.
Can some1 please elaborate for me. Thanks

Is that formula part of a larger formula?

That particular expression is typically used in a larger formula that does a lookup when there are multiple instances of the lookup value.

Like this:

=INDEX(B2:B9,SMALL(IF(A2:A9=I6,ROW(A2:A9)-ROW(A2)+1),N))

Where N is the nth instance of the lookup value that you want to find.

You can eliminate that "offset correction" by indexing the entire column:

=INDEX(B:B,SMALL(IF(A2:A9=I6,ROW(A2:A9)),N))

 

[Aladin Akyurek]

Thanks, my I6=3, but why is it that after the '-row(A2)+1', the answer is still 8? not 6?(8-6)

In the formula

=SMALL(IF(A2:A10=I6,ROW(A2:A10)-ROW(A2)+1),2)

the IF bit returns for each A-cell from A2:A10 which is equal to I6 that cell's row number minus the row number of the first cell of A2:A10 plus 1. Such yields something like:

{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;7;8;9}

The formula maps A2:A10 to a vector of 1, 2, 3, 4, ...,8, 9

The 2nd smallest yields: 8.

If you would want the native row number as result, the mapping must be direct:

=SMALL(IF(A2:A10=I6,ROW(A2:A10)),2)

Note. A2:A10 is assumed to house the Machine # values and I6 = 3...

<TABLE style="WIDTH: 74pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=98><COLGROUP><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3498" width=98><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 74pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=98>Machine #

</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19 align=right>1</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19 align=right>1</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19 align=right>1</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19 align=right>2</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19 align=right>2</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19 align=right>2</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19 align=right>3</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19 align=right>3</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19 align=right>3</TD></TR></TBODY></TABLE>

 

[T. Valko]

Is that formula part of a larger formula?

That particular expression is typically used in a larger formula that does a lookup when there are multiple instances of the lookup value.

Like this:

=INDEX(B2:B9,SMALL(IF(A2:A9=I6,ROW(A2:A9)-ROW(A2)+1),N))

Where N is the nth instance of the lookup value that you want to find.

You can eliminate that "offset correction" by indexing the entire column:

=INDEX(B:B,SMALL(IF(A2:A9=I6,ROW(A2:A9)),N))

Here's an example:

Lookup when there are multiple instances of the lookup value

http://www.mrexcel.com/forum/showpost.php?p=2696961&postcount=3