To get values into an array

D

Darren1o1

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
 
B

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)
 
D

Darren1o1

The value in the box then comes up as false.

Bob Phillips said:
=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)
Click to expand...
 
B

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)
 
D

Darren1o1

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
 
B

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)
 
H

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.
Click to expand...
....

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.
 
Top