Array Formula Challenge

[Arul.rajesh]

Data is organised like this:


__A__B

1 15 inc
2 23 inc
3 53 inc
4 34 inc
5 67 exc
6 27 inc
7 26 inc
8 22 exc
9 93 inc


Is there a possibility to write an array formula, which would return the array {15,23,53,34,27,26,93}, Basically wherever column "B" contains "inc"?


I can do this in VBA by looping through all the rows, but I was looking for a more elegant solution.

 

[Aladin Akyurek]

What is the expected usage?

 

[Arul.rajesh]

I need to calculate quartiles on the resultant range..its like a =quartileif() function.

 

[Arul.rajesh]

I need to calculate quartiles on the resultant range..its like a =quartileif() function.

In column B I have about 300 unique values and about 800,000 rows of data. I have about 25 sheets like this.

So I need to calculate the quartile for each value in column B for the 25 sheets.

FYI
for example
3rd Quartile tells you in a given sample of data which number would fall at the 75% position, (for lack of a better word in my vocabulary). The nth quartile might not necessarily be from the sample.

I am not able to explain this properly.

but that is what i need this for..... Looping is a time intensive solution.

 

[Aladin Akyurek]

I need to calculate quartiles on the resultant range..its like a =quartileif() function.

Example:

=QUARTILE(IF(B2:B10="inc",A2:A10),3)

which must be confirmed with control+shift+enter, not just enter.

 

[Arul.rajesh]

Example:

=QUARTILE(IF(B2:B10="inc",A2:A10),3)

which must be confirmed with control+shift+enter, not just enter.

Hmm that is a good solution.. I was under the impression that text/FALSE would evaluate to a 0 and skew the Quartile calculation.

Thanks for the solution..

 

[Aladin Akyurek]

Hmm that is a good solution.. I was under the impression that text/FALSE would evaluate to a 0 and skew the Quartile calculation.

Thanks for the solution..

You are welcome. Thanks for providing feedback.