Average Top Mid and Bottom

Okay I apoligize if this has been posted before I couldn't find anything that would help me.

I am needing to do a formula based on a range of data that will return the top third middle third and bottom third.



Example Data
W X Y

Gross Gross
Margin Margin% Rank
51,241 36.46% 2
25,584 19.56% 20
19,914 16.26% 40
17,188 14.27% 52
19,167 15.55% 43
6,828 5.23% 84
48,076 29.17% 3
18,282 13.78% 47
16,896 12.75% 58
16,124 12.14% 64
16,549 12.27% 59
17,021 14.07% 56
17,128 14.44% 53
18,742 15.23% 46
17,547 14.61% 49
14,550 12.19% 69
15,552 12.94% 67
12,982 7.14% 73
14,896 8.19% 68
21,010 9.01% 34
25,646 38.39% 19
20,011 16.54% 38
18,962 15.77% 44
19,480 16.50% 42
9,803 9.24% 79
9,420 8.88% 80
4,627 4.36% 86
4,891 4.61% 85
19,985 16.56% 39
14,427 9.86% 70
60,256 35.29% 1
21,511 12.63% 30
16,295 14.89% 61
15,715 14.36% 66
17,126 16.15% 54
17,090 16.11% 55
22,949 21.05% 25
28,381 25.42% 15
31,899 28.12% 10
13,532 12.76% 71
3,795 4.62% 89
4,062 5.02% 87
1,090 1.39% 90
1,072 1.36% 91
(5,524) -6.56% 94
(5,334) -6.33% 93
(5,189) -6.16% 92
21,473 19.23% 31
21,066 18.87% 33
24,867 22.34% 23
10,257 8.93% 78
7,424 6.46% 82
10,736 9.35% 77
24,927 22.72% 22
24,548 22.94% 24
22,412 20.93% 26
16,910 10.41% 57
16,164 14.95% 63
20,345 14.86% 37
(8,630) -9.99% 95
16,480 10.60% 60
3,974 2.45% 88
22,298 13.99% 27
31,390 28.72% 11
28,790 16.96% 13
20,952 15.14% 35
35,187 21.87% 8
29,341 14.21% 12
16,116 23.86% 65
21,224 15.24% 32
39,810 19.25% 6
26,197 23.72% 17
25,890 23.45% 18
21,768 19.05% 29
25,189 22.05% 21
19,758 20.69% 41
40,662 29.19% 5
39,802 28.58% 7
42,141 21.31% 4
11,796 12.27% 76
12,149 12.64% 74
12,001 12.48% 75
17,328 13.99% 50
22,075 17.84% 28
18,780 15.17% 45
16,246 13.13% 62
20,792 16.80% 36
17,258 13.94% 51
18,212 14.71% 48
28,565 19.89% 14
13,346 17.65% 72
32,867 23.42% 9
7,470 6.89% 81
7,285 6.72% 83
27,305 18.15% 16

I first need it to look in the rank column for highest number and divide by 3

Then I need it to give me the average Gross Margin if rank is between 1-32, 33-64, 65-95

I have this for the top and bottom but can't get the mid range to work(except it doesn't automatically calculate)

SUMIF($Y$8:$Y$102,"<33",$W$8:$W$102)/32
I have tried averageif....


Using rank to calculate this may not be the most efficent way

I hope that makes sense - I thank you so much for any help

How about (in B3)

Please Login or Register  to view this content.

Does that work for you? See Excel's built in help on VLOOKUP.