| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
To get values into an array
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
To get values into an array
=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) "Darren1o1" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
To get values into an array
The value in the box then comes up as false.
"Bob Phillips" wrote: =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) "Darren1o1" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
To get values into an array
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) "Darren1o1" wrote in message ... The value in the box then comes up as false. "Bob Phillips" wrote: =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) "Darren1o1" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
To get values into an array
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" wrote: 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) "Darren1o1" wrote in message ... The value in the box then comes up as false. "Bob Phillips" wrote: =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) "Darren1o1" wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
To get values into an array
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",RO W($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) "Darren1o1" wrote in message ... 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" wrote: 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) "Darren1o1" wrote in message ... The value in the box then comes up as false. "Bob Phillips" wrote: =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) "Darren1o1" wrote in message ... 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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
To get values into an array
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 Darren1o1 wrote: 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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
To get values into an array
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($A 1:$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. "Darren1o1" wrote in message 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 The opposite of feasible is infeasible, not unfeasible. to get the measurements of the "Feasible" data into of an array. Eg. .... 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(A 1:A6,"F"),1))),0,1,1)) This can be used as a term in longer formulas, but it can't be nested very deeply. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Filling Source Data Array with Decimal Values | Charts and Charting in Excel | |||
| How to find the largest product of an array of values? | Excel Worksheet Functions | |||
| Help with an array function | New Users to Excel | |||
| Array Manipulation | Excel Worksheet Functions | |||
| Formula to list unique values | Excel Worksheet Functions | |||
Linear Mode
