How To... array formula that filters by multiple criteria on same column

[jprealini]

Hi...

Basically what I have is a function that will calculate Percentile based on an array formula

The formula goes like this

=PERCENTILE(SI('RAW C TIME'!$D$2:$D$51816=$J7,IF('RAW C TIME'!$J$2:$J$51816=$K$4,IF($K$3="(All)",'RAW C TIME'!$I$2:$I$51816,IF('RAW C TIME'!$G$2:$G$51816=$K$3,'RAW C TIME'!$I$2:$I$51816)))),N$6)

The last IF is filtering by the value in $K$3, which is the year... Now, we need to add support for this to return records not just for one year... so if in the filter cell we choose 3 options (2011,2012,203) of the 8 possible options, we need the filter to use those 3 values

I read somewhere that that would be solved like this

IF((CRITEARIA A)*(CRITERIA B)*...*(CRITERIA N), VALUE IF TRUE, VALUE IF FALSE)

I tried this, first by just hardcoding the values for K3, but I am not getting any value...

My questions are...

1. Is this the right way to do this filtering?
2. Is the "*" working as an AND or an OR in that case? Or is it something different?

Thanks

[ajryan88]

1. Could you please upload your workbook to let me help you answer that question
2. The "*" act similarly to an AND, but the AND function cannot return an array, whereas the * can return an array. Each condition will return TRUE/FALSE (or 1/0), and multiplying them together forces all values to be 1 (or TRUE) for the expression to evaluate to 1 (or TRUE), as multiplying anything by 0 (or FALSE) will force the entire expression to be 0 (or FALSE). I hope this answers question 2 satisfactorily for you

Thanks

[Vikas_Gautam]

Use * for AND.. and + for OR
Try this

Please Login or Register  to view this content.

Don't forget to click *

[jprealini]

Thanks! This sure helped