Percentile Calculation

[coolkiran]

Hello

i am calculating percentile of a column, using "=Percentile(column, 0.5)"

But my situation here, is i have 2 columns one is country and second column is salary.

I want to calculate country wise median percentile, i used below formula to calculate :

=PERCENTILE(IF(A2:A6275=G6,B:B),0.25)

I am getting output, but that is not the correct one.

I have attached the example file. Any one please check and let me know where i am missing.

 

[Khalid NGO]

Hi,

I think it is because of #N/A in your column A:

Try this with CSE:
=PERCENTILE(IF(IFERROR($A$2:$A$6275=$G6,0),$B$2:$B$6275),H$5/100)
copy to right and down.

Regards,

 

[coolkiran]

Thanks for your reply.

Still i am not getting correct answer, if i took all "asian" and added formula, i am getting output as "6669". Now i am getting output as "7500" and for remaining field "Arab" showing as "0" actually it should be "8700".

 

[Khalid NGO]

Thanks for your reply.

Still i am not getting correct answer, if i took all "asian" and added formula, i am getting output as "6669". Now i am getting output as "7500" and for remaining field "Arab" showing as "0" actually it should be "8700".

I am sure you are pressing just enter.

I mentioned earlier, "Try this with CSE:"
that means this is an array formula which must be entered with Ctrl+Shift+Enter and not just enter.
If you done it correctly you will see {} curly brackets at start and end, and your formula will looks like:
{=PERCENTILE(IF(IFERROR($A$2:$A$6275=$G6,0),$B$2:$B$6275),H$5/100)}

Try one more time...
Regards,

 

[coolkiran]

Thanks, its perfect now.

 

[Khalid NGO]

Thanks, its perfect now.

Glad you've done it.
you are welcome and thanks for the feedback.