CODEWITHSUNDEEP
excelexcel-formulaexcel-2010
dmorgan20
dmorgan20

Reputation: 363

Percentile difficulty in excel

I am trying to calculate percentile based on multi criteria but it seems to be completely ignoring my criteria - Help!

Column A - Manager Name Column B - Calls Answered

Formulas I have tried:

=IF(AS:AS=AS2,IF(G2<PERCENTILE.EXC(G:G,33.33333%),"Bottom",IF(G2<PERCENTILE.EXC(G:G,66.66666%),"Middle","Upper")))

Also used:

=IF(AND(AS:AS=AS2,B:B=B2,G2<PERCENTILE.EXC(G:G,33.33333%)),"Bottom",IF(AND(AS:AS=AS2,B:B=B2,G2<PERCENTILE.EXC(G:G,66.66666%)),"Middle","Upper"))

Upvotes: 0

Views: 64

Answers (1)

Scott Craner
Scott Craner

Reputation: 152660

Use this:

=IF(G2<AGGREGATE(18,6,G$2:G$1000/(AS$2:AS$1000=AS2),.3333333),"Bottom",IF(G2<AGGREGATE(18,6,G$2:G$1000/(AS$2:AS$1000=AS2),.66666666),"Middle","Top"))

But one note. One should only use confined date set reference and avoid full column references when using array type formula.

Upvotes: 2

Related Questions