CODEWITHSUNDEEP
excel
Archit Jain
Archit Jain

Reputation: 2234

MS Excel PERCENTILE and IF doesn't give correct result

I am trying to get Percentile for selective data. Here in this example I am trying to get 10 percentile of scores where player name is a

Data : 

      | A |  B
    ------------
     1| a |   1
     2| a |   2
     3| a |   3
     4| a |   4
     5| a |   5
     6| a |   6
     7| a |   7
     8| a |   8
     9| a |   9
    10| a |   10
    11| b |   11
    12| b |   12
    13| b |   13
    14| b |   14
    15| b |   15
    16| b |   16
    17| b |   17
    18| b |   18
    19| b |   19
    20| b |   20

Formula : 

=PERCENTILE.EXC(IF(A:A="a",B:B,None),0.1)

Expected Output : 

1.1

Actual Output : 

2.1

Upvotes: 0

Views: 80

Answers (1)

A.S.H
A.S.H

Reputation: 29332

You are using an Array Formula (aka CSE formula), so you should enter it then press

CtrlShiftEnter

If you want a normal formula, use Aggregate which by nature handles arrays in a transparent way, with parameter 18 = PERCENTILE.EXC:

=AGGREGATE(18,6, B:B/(A:A="a"), 0.1)

Upvotes: 1

Related Questions