Percentile

Need to calculate different percentiles based on different grades. how do I put an 'if' loop or anything else that will work for this.

Grade Amount
B2 3
B2 2
B3 5
B3 6
B4 8
B4 9
B5 10
B5 11
B6 15
B2 3
B3 7
B6 14



Need function for the table below
Percentile 10% 25% 50% 60% 75% 90%
B2
B3
B4
B5

I don't know what you're trying to do, but PERCENTILE or PERCENTRANK is probably what you want. See Help.

I am trying to get 10th Percentile for B2 Grade under Cell B22. And then 25th percentile for B2 Grade.

Similarly for all grades.

I tried Percentrank/Percentile but not able to figure out because I need to fit a condition to pick up that Grade 'only' from the data table.

Thanks and regards

I am still only guessing what you want. Maybe this:

Please Login or Register  to view this content.

The formula in C2 and copied across and down is

=PERCENTILE(IF($A$2:$A$13=$A2, $B$2:$B$13), C$1)

The formula MUST be confirmed with Ctrl+Shift+Enter instead of just Enter.

I'm not a statistician but if you are just looking at the sum of each group and calculating percentages from that sum, I did it as attached using SUMIF and VLOOKUP. I created a table for VLOOKUP by listing Grades from cell D3 to D7 and in Column E

Please Login or Register  to view this content.

dragged down,
Then in your percentage table starting in B22

Please Login or Register  to view this content.

dragged across and down.
Is that what you are looking for?

Bingo SHQ !!!!

That's what I wanted, but was getting confused with the IF loop. You made it look so easy

I have another quick one, how do I include an AND in the IF in this case. E.g. if I have two more categories Voice & Data (in another column) and want B2 & Voice Percentile, B2 & Data Percentile.

Thank you ChemistB, but it is the percentiles that I wanted by Grade, not summing. Thank you anyways.

Cheers !

How do I in addition ?

AND in the IF in this case. E.g. if I have two more categories Voice & Data (in another column) and want B2 & Voice Percentile, B2 & Data Percentile.

You add AND() in IF() this ways -

Please Login or Register  to view this content.

I did, it doesnt work. I've to use it as an array formula right?

Here is what I am doing but it is giving ZERO as value :

=PERCENTILE(IF(AND(BaseDataMay09!$E3:$E3001=$A$12,BaseDataMay09!$I3:$I3001=$A14),BaseDataMay09!$L3:$L3001),C$4)

Originally Posted by sachinattri

=PERCENTILE(IF(AND(BaseDataMay09!$E3:$E3001=$A$12,BaseDataMay09!$I3:$I3001=$A14),BaseDataMay09!$L3:$L3001),C$4)

Sachin,

Are you using the above formula as Array na?

{=PERCENTILE(IF(AND(BaseDataMay09!$E2:$E3000=$A$12,BaseDataMay09!$I2:$I3000=$A13),BaseDataMay09!$L2:$L3000),C$4)}

yes as Array but it is giving 0.00 as a response.

Any solutions?

Post an example.