What's wrong with the formula?

[Chemeng]

Hi,

I have a list of data in column C of which I want to calculate the 1/3rd percentile where certain conditions are met in columns A and B.

I have used the following formula to calculate the answer where I only have two conditions in separate columns which does not seem to work.

{=PERCENTILE.EXC(IF(AND(Data!A:A<"U",Data!B:B<"V"),Data!C:C,""),1/3)}

The result is #value.

[tim201110]

=PERCENTILE.EXC(IF((Data!A:A<"U")*(Data!B:B<"V"),Data!C:C,"")),1/3)
You can't use the AND and OR functions in array formulas directly because those functions return a single result, either TRUE or FALSE, and array functions require arrays of results. You can work around the problem by using the logic shown in the previous formula. In other words, you perform math operations, such as addition or multiplication, on values that meet the OR or AND condition.

[Chemeng]

Thanks.

So, AND function can't be used for array?

[AliGW]

That's what he said.

For future reference: please take a moment to re-read forum rule #1.

• Use concise, accurate thread titles.
• Your post title should describe your problem, not your anticipated solution.
• Use terms appropriate to a Google search - poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice tell us nothing.
• Responding to a request to change your thread title by doing so is mandatory.

[Chemeng]

Oh, it wasn't showing the explanation below the formula before, but now it is. Thanks anyways.

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.