To get values into an array

[Guest]

I need a function to check a Column of data and check if it is "Feasible" or
"Unfeasible". In the next column of data the is measurements. I need to get
the measurements of the "Feasible" data into of an array.
Eg.
Column a = Feasible?
Column b = Inductance (Tesla)

Feasible? Inductance (Tesla)
Feasible 1,17621E-06
Feasible 1,59844E-06
Feasible 2,05083E-06
Feasible 2,5937E-06
Feasible 3,16673E-06


Should end up with the inductance values in an array if they are feasible.
Thanks

 

[Bob Phillips]

=IF(A1:A100="Feasible",B1:B100)

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

The value in the box then comes up as false.

=IF(A1:A100="Feasible",B1:B100)

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Bob Phillips]

It returns an array, which is what you aid you wanted. You need to do
something with that array, embed it in another formula.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

What i need is to see the values, these are the output of the formulae (the
feasible size numbers for the project) how could i do this?? thanks again

 

[Bob Phillips]

Select the range where you want to see the results (all of them), then enter
this into the formula bar

=IF(ISERROR(SMALL(IF($A$1:$A$20="Feasible",ROW($A1:$A20),""),ROW($A1:$A20)))
,"",
INDEX($B$1:$B$20,SMALL(IF($A$1:$A$20="Feasible",ROW($A1:$A20),""),ROW($A1:$A
20))))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Alan Beban]

Ifthe functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook

=VLookups("Feasible",a2:b6,2) array entered into a column long enough to
accommodate the output.

Alan Beban

 

[Harlan Grove]

Bob Phillips wrote...

Select the range where you want to see the results (all of them), then enter
this into the formula bar

=IF(ISERROR(SMALL(IF($A$1:$A$20="Feasible",ROW($A1:$A20),""),
ROW($A1:$A20))),"",INDEX($B$1:$B$20,SMALL(IF($A$1:$A$20="Feasible",
ROW($A1:$A20),""),ROW($A1:$A20))))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

....

I really don't think you understand the nature of the array the OP
wants. Of course, the OP provided a poor example of what he needs.

The opposite of feasible is infeasible, not unfeasible.
....

Replacing your example with

Feasible? Inductance
Feasible 1
Infeasible 2
Feasible 3
Feasible 4
Infeasible 5
Feasible 6

do you want an array like either {1;FALSE;3;4;FALSE;6} or
{1;"";3;4;"";6}, or do you want an array like {1;3;4;6}? Bob has
provided formulas for both of the former. If you want the latter, then
it requires a volatile OFFSET call.

N(OFFSET(B1:B6,SMALL(IF(A1:A6="F",ROW(B1:B6)-ROW(INDEX(B1:B6,1,1))),
ROW(INDEX($1:$65536,1,1):INDEX($1:$65536,COUNTIF(A1:A6,"F"),1))),0,1,1))

This can be used as a term in longer formulas, but it can't be nested
very deeply.