Calculating Percentile using Aggregate function and IF statement

[nimah]

[h=2]APercentil calculation usind Aggregate and IF statement[/h]

I am trying to calculate Percentile using Aggregate function and If with AND Statement. Following is what I have in Columns C, D and E in cells 1 to 10. (10 items)

Column C: Some cells have text A. Some cells are blank.
Column D: Some columns have text B. Others C value. No blank cell.
In column E, I have then values typed in (numbers 10, 20, 30,........,90,100).

I want Excel to check columns D and C and if column D indicate "B" and Column C indicate "A", then calculate Percentile for those rows.

I use the following formula and want to calculate 10th percentile:

{=AGGREGATE(16,6,$E$1:$E$10/IF(AND($C$1:$C$10="A"),($D$1:$D$10="B")),0.1)}

Then I change 0.1 to 0.2, 0.3, 0.4, .......,0.9, and 1.

I tried this. I get correct value for the rows that have both values A (in C Column) and B (in column D. It does not return a value if a cell under C is blank or the values under column D is C. For these cases, it returns #NUM!.
It does not like blank cells under C Column and C value under D column.

I appreciate any help. May be I need to change something in the formula.

Nima​

 

[Marcelo Branco]

See if this does what you need

=AGGREGATE(16,6,$E$1:$E$10/(($C$1:$C$10="A")*($D$1:$D$10="B")),0.1)
confirmed with just Enter

M.

 

[nimah]

See if this does what you need

=AGGREGATE(16,6,$E$1:$E$10/(($C$1:$C$10="A")*($D$1:$D$10="B")),0.1)
confirmed with just Enter

M.

It works. Thank you. Can you explain?

 

[Marcelo Branco]

You are welcome.

About the formula:
1. The AGGREGATE function handles arrays without the need for CSE.
2. The second argument 6 means: Ignore error values
3. In array formulas an AND condition should be used by multiplying the conditions

To see, step by step, what the formula does try
Formulas > Evaluate formula

M.