Finally figured out how to make my array work ,now can anyone tell me why if
you have got an array
example
{=INDEX(A$10:A$170;SMALL(IF($w$10:$w$170;ROW($w$10:$w$170);"");ROW()))}
The ROW($w$10:$w$170) term will return the array {10..170} rather than {1..161}.
You seem to want/need the latter rather than the former.
or another example
{=INDEX(A$10:A$170,SMALL(IF($W$10:$W$170=TRUE,ROW($W$10:$W$170),""),ROW()-17
0))}
IF($W$10:$W$170,...) and IF($W$10:$W$170=TRUE,...) are identical. Checking
whether some range equals TRUE is a waste of memory and processor time.
The ROW($W$10:$W$170) term is still screwed up. The ROW() term determines which
(to be technical) order statistic, from smallest to largest, to pull from the
sample given as the 1st argument to SMALL. If you're entering these formulas
into cells in row 171 and lower, then you've fixed a problem in the first
formula. Impossible to say for sure because ROW() [and COLUMN()] as such returns
different values depending on the cell from which it's called.
why will it not work properly, what it is doing its starting further down in
the row like 10 rows down, what I did was the following
=INDEX(A$1:A$170,SMALL(IF($W$1:$W$170=TRUE,ROW($W$1:$W$170),""),ROW()-170))
I started the array at 1,even though my numbers don't start till row 10
any ideals?
Presumably W1:W9 are all blank, so all these generate False in the IF condition.
If so, then this works because the sample values in SMALL's 1st argument finally
correspond to the values you want in column A.
If you were to enter the topmost formula in any cell XYZ (as an ABSOLUTE
reference), the generic formula (i.e., self-contained with no hard-coded numeric
constants) should be
=INDEX(A$10:A$170,SMALL(
IF($W$10:$W$170,ROW($A$10:$A$170)-CELL("Row",$A$10:$A$170)+1,""),
ROW()-ROW(XYZ)+1))
This ENSURES that the sample fed to small is {1..161} rather than {10..170}.
This comes from the ROW(Range)-Cell("Row",Range)+1 idiom, which is the generic
way to generate a sequence of row indices into Range. In your case, A10:A170 has
row *INDICES* 1, 2, . . ., 161, *NOT* 10, 11, . . ., 170.
You have to distinguish between row *NUMBER* in the worksheet as a whole and row
*INDEX* into a range in the worksheet. The two are not the same.
Finally, *ALWAYS* put follow-ups in the same thread.
