Array problem

  • Thread starter Thread starter David W
  • Start date Start date
D

David W

Cant get this to work, it should but it don't
Any Ideals

=INDEX(A$10:A$170,SMALL(IF($W$10:$W$170=TRUE,ROW($W$10:$W$170),""),ROW()-170
))
 
What would you like this formula to do? What happens when you enter the
formula on a worksheet with the appropriate data in the referenced ranges?
 
I have got a spread with rows A10:A170
in that column I have got index numbers 1-160
there are 11 columns to the right with data in them

in column w there is true/false (=b10>0,true)

what I am trying to get it to do is to find cerain rows with data(looking in
column b) and return the results in order as they appear from smallest to
largest
I put that formula in ,hit F2 then crtl+shift and enter then filled down and
across. it should work but it dont

{=INDEX(A$10:A$170,SMALL(IF($W$10:$W$170=TRUE,ROW($W$10:$W$170),""),ROW()-17
0))}
 
David

Did you Array entered, thus with CTRL+SHIFT+ENTER?

Pieter
| Cant get this to work, it should but it don't
| Any Ideals
|
| =INDEX(A$10:A$170,SMALL(IF($W$10:$W$170=TRUE,ROW($W$10:$W$170),""),ROW()-170
| ))
|
|
 
What's in W10:W170? Under what circumstances is $W$10:$W&170 expected to
return TRUE?

Alan Beban
 
David

Just did a little test
In Column A the numbers 1-10
In Column B true or nothing

This array formula works fine
=INDEX(A$1:A$10;SMALL(IF($B$1:$B$10;ROW($B$1:$B$10);"");ROW()))
In row 1 the smallest
In row 2 the next one and so on


=INDEX(A$10:A$170,SMALL(IF($W$10:$W$170=TRUE,ROW($W$10:$W$170),""),ROW()-170))

Where do you enter this formula, ROW()-170 must give a number >0
In Column W must have TRUE and not a number or text

So give us a bit more information

Pieter




| yeap
|
|
 
Back
Top