Unexpected result

[Biff]

Hi Folks!

Can someone explain the result I'm getting:

A1 = 11
A2 = 12
A3 = 13
A4 = 14
A5 = 15

A10 = empty

I want the formula to extract the values in A1:A5 IF A10 = X

Formula: (array entered)

=INDEX(A$1:A$5,SMALL(IF(A$10="X",ROW($1:$5)),ROW(1:1)))

Copied down 5 cells returns:

11
#NUM!
#NUM!
#NUM!
#NUM!

I should get #NUM! in every cell.

Here's where I don't understand the result of the first cell return of
11....

Evaluating the formula and stepping through:

SMALL(FALSE,{1}) evaluates to SMALL(0,1) = 0

So, =INDEX(A$1:A$5,0)

Returns the value in the first position in the array A1:A5, 11.

=INDEX(A$1:A$5,1) also returns the value in the first position in the array
A1:A5, 11.

I would think that there is no zero position in the array and the formula
should error.

I know that the above #NUM! errors are being generated by the SMALL function
but shouldn't INDEX also generate an error based on position zero?

I'm confiussed on this!

Thanks

Biff

 

[mangesh_yadav]

Hi Biff,

Try entering the following in a cell
=INDEX(A1:A5,0)

Case 1: as an array formula, and
Case 2: as a simple formula

In the first case, it returns 11 (and if you drag down, the subsequen
numbers). In the second case, it returns #VALUE and the same fo
dragging down.

In you main formula, the above part is treated as an array formula an
so you get 11 in the first case, whereas in the subsequent case
instead of 0 you get #NUM as the second argument for the index functio
and so you get #NUM

Manges

 

[mangesh_yadav]

Hi Biff,

your question and the solution aaray did not match. Why don't yo
simply use:
=IF($A$10="X",A1,"") and copy down
or
=IF($A$10="X",A1:A5,"") as an array formula by selecting all the fiv
cells in the column.

Manges

 

[Aladin Akyurek]

=INDEX(A1:A5,0)

which is identical to the full version:

=INDEX(A1:A5,0,1)

means all of the rows of A1:A5.

The formula cell will house the result given your sample:

={11;12;13;14;15}

with the topleft cell displaying.

The foregoing also holds for:

=INDEX(A1:A5,{0})

Intermezzo: Invoke =INDEX($A$1:$A$5,0) in a cell in the same worksheet
you want to data validate as Source. That cell will show you all of the
values from A1:A5.

BTW, ROW(1:1) in

=INDEX(A$1:A$5,SMALL(IF(A$10="X",ROW($1:$5)),ROW(1:1)))

makes the formula non-robust (therefore incorrect) against row
insertions before the formula row.

 

[Biff]

Thanks for your input.

Actually, I was just "playing" around with some stuff and couldn't quite
figure out what I was seeing.

Biff

"mangesh_yadav" <[email protected]>
wrote in message
news:[email protected]...

 

[Biff]

Ok, that makes sense!

Biff

=INDEX(A1:A5,0)

which is identical to the full version:

=INDEX(A1:A5,0,1)

means all of the rows of A1:A5.

The formula cell will house the result given your sample:

={11;12;13;14;15}

with the topleft cell displaying.

The foregoing also holds for:

=INDEX(A1:A5,{0})

Intermezzo: Invoke =INDEX($A$1:$A$5,0) in a cell in the same worksheet you
want to data validate as Source. That cell will show you all of the values
from A1:A5.

BTW, ROW(1:1) in

=INDEX(A$1:A$5,SMALL(IF(A$10="X",ROW($1:$5)),ROW(1:1)))

makes the formula non-robust (therefore incorrect) against row insertions
before the formula row.