gtd526
Well-known Member
- Joined
- Jul 30, 2013
- Messages
- 657
- Office Version
- 2019
- Platform
- Windows
Hello,
Im trying to Rank AB7 & AB8 using S4:S36.
AC7 = Rank
AC8 = Rank
There are NO merged cells.
Im trying to Rank AB7 & AB8 using S4:S36.
AC7 = Rank
AC8 = Rank
There are NO merged cells.
AMTD Funds.xlsm | |||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
S | T | U | V | W | X | Y | Z | AA | AB | AC | |||
4 | 8.9% | $0 | 23 | 25 | 20 | 5 | |||||||
5 | 8.9% | 0 | 0 | 0 | Disqualifing 2x Funds | ||||||||
6 | Rank | ||||||||||||
7 | 10.9% | $1,000 | 15 | 17 | 14 | 3 | RYVYX | 20.3% | #N/A | ||||
8 | 12.2% | $2,500 | 8 | 10 | 8 | 2 | RMQHX | 19.8% | |||||
9 | 10.9% | $2,500 | 14 | 16 | 13 | 3 | |||||||
10 | 10.0% | $2,500 | 19 | 21 | 18 | 3 | |||||||
11 | 9.0% | $2,500 | 22 | 24 | 19 | 5 | |||||||
12 | 10.0% | $2,500 | 18 | 20 | 17 | 3 | |||||||
13 | 10.1% | $3,000 | 17 | 19 | 16 | 3 | |||||||
14 | $2,500 | 0 | 0 | 2x | |||||||||
15 | $2,500 | 10.4% | 0 | 0 | 2x | ||||||||
16 | 0 | ||||||||||||
17 | 11.4% | $0 | 11 | 13 | 10 | 3 | |||||||
18 | 12.5% | $0 | 6 | 8 | 6 | 2 | |||||||
19 | 11.0% | $0 | 13 | 15 | 12 | 3 | |||||||
20 | 11.4% | $500 | 12 | 14 | 11 | 3 | |||||||
21 | 11.4% | $0 | 10 | 11.5% | 12 | 9 | 3 | ||||||
22 | |||||||||||||
23 | 14.5% | $2,500 | 2 | 4 | 2 | 2 | |||||||
24 | 14.7% | $2,500 | 1 | 3 | 1 | 2 | |||||||
25 | 12.6% | $2,500 | 5 | 7 | 5 | 2 | |||||||
26 | 12.5% | $0 | 7 | 9 | 7 | 2 | |||||||
27 | 7.2% | $2,500 | 24 | 26 | 21 | 5 | |||||||
28 | 10.8% | $2,500 | 16 | 18 | 15 | 3 | |||||||
29 | 11.5% | $2,500 | 9 | 11 | 8 | 3 | |||||||
30 | 6.6% | $2,500 | 27 | 29 | 21 | 8 | |||||||
31 | 9.3% | $0 | 20 | 22 | 18 | 4 | |||||||
32 | 7.2% | $2,500 | 25 | 27 | 21 | 6 | |||||||
33 | 7.1% | $2,500 | 26 | 28 | 21 | 7 | |||||||
34 | 9.3% | $2,500 | 21 | 23 | 18 | 5 | |||||||
35 | 12.6% | $2,500 | 4 | 6 | 4 | 2 | |||||||
36 | 13.8% | $2,500 | 3 | 10.7% | 5 | 3 | 2 | ||||||
ALL |
Cell Formulas | ||
---|---|---|
Range | Formula | |
S4,S23:S24,S17:S21,S10:S13,S7:S8 | S4 | =$R4/$B4 |
T4,T17:T21 | T4 | =VLOOKUP(A4,'Funds 2'!$A$3:$F$8,6,0) |
U4,U23:U36,U17:U21,U7:U13 | U4 | =SUMPRODUCT((S4<=$S$4:$S$36)/COUNTIF($S$4:$S$36,$S$4:$S$36)) |
W23:W36,W17:W21,W7:W15,W4:W5 | W4 | =IF($U4=0,0,MAX($U4,$W4)) |
X23:X36,X17:X21,X7:X13,X4:X5 | X4 | =IF($X4=0,$U4,MEDIAN($X4,$U4,0)) |
Y23:Y36,Y17:Y21,Y7:Y15,Y4:Y5 | Y4 | =W4-X4 |
V5 | V5 | =AVERAGE(S4:S5) |
AC7 | AC7 | =RANK(AB7,S4:S36) |
AB7:AB8 | AB7 | =$R14/$B14 |
S9,S25:S36 | S9 | =($Q9-$B9)/$B9 |
T7:T15 | T7 | =VLOOKUP(A7,'Funds 1'!$A$3:$F$12,6,0) |
V15 | V15 | =AVERAGE(S7:S15) |
V21 | V21 | =AVERAGE(S17:S21) |
T23:T36 | T23 | =VLOOKUP(A23,'T Rowe Price'!$A$2:$F$15,6,0) |
V36 | V36 | =AVERAGE(S23:S36) |