Percetile complications

Question

I am trying to find the bottom 33%, middle 33% and upper 33% from my dataset.

What I need: I need to get the percentile for individuals in excel. I have hundreds of people and next to them the managers name so I have been using the below formula to get the percentile based on manager.

Current Formula:

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

Problem:

This formula should have the words top, middle and bottom against every manager at least once. But I have noticed some managers only having a top and middle. Clearly I am missing something?

Update: I have also used the below formula against just the numbers and not receiving 'bottom' at all

=IF(A1<PERCENTILE.EXC(A:A,33.33333%),"Bottom 33%",IF(A1<PERCENTILE.EXC(A:A,66.666666%),"Middle","Upper"))

SAMPLE NUMBERS:

6.31025416
5.18260342
5.25185395
4.57484582
4.99563873
6.31717482
3.49576271 
4.21992744
5.02853494
4.77338476
4.9579394
4.00174679
4.04134247
4.66614821
4.264681
1.94515737
3.96117421 0 0
1.35109777 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0.97126669
0.99805933 0 0 0

Answer 1

If you want to exclude the 0 use this:

=IF(G2<>0,IF(G2<AGGREGATE(18,6,G$2:G$1000/((AS$2:AS$1000=AS2)*(G$2:G$1000<>0)),0.3333333),"Bottom",IF(G2<AGGREGATE(18,6,G$2:G$1000/((AS$2:AS$1000=AS2)*(G$2:G$1000<>0)),0.66666666),"Middle","Top")),"")