Ranking

[gtd526]

Hello,
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)

 

[Peter_SSs]

The RANK function "Returns the rank of a number in a list of numbers." The number that you are trying to rank is not in the list of numbers - hence the error.
In fact the values in AB7 & AB8 are larger than any of the values in S4:S36 so very unclear to me what you are trying to do.

So you would need to explain better exactly what you would want returned in AC7 & AC8 and how you would determine those results if you were doing the task manually.

 

[gtd526]

The RANK function "Returns the rank of a number in a list of numbers." The number that you are trying to rank is not in the list of numbers - hence the error.
In fact the values in AB7 & AB8 are larger than any of the values in S4:S36 so very unclear to me what you are trying to do.

So you would need to explain better exactly what you would want returned in AC7 & AC8 and how you would determine those results if you were doing the task manually.

I wanted to exclude them from the original ranking (U:U) but see how they rank, if possible.
I was trying to get around the "list of numbers", the value is not part of the list.
I didn't want their rank to affect the others. (the values will change, so rank will change)
I use Sumproduct in U:U in case there's a duplicate (% to rank), so it doesn't skip a rank number.
Question:
Why does the Rank(AB7&8) work when I populate the S:S value, but if I delete the value it doesn't? see below.
The S:S are only what I want to rank against, not the value to rank (AB7). S:S contains blank cells, so deleting the value shouldn't matter??
If needed, I will consider Rank #3 as #1.
Thanks for the input.

AMTD Funds.xlsm
	S	T	U	V	W	X	Y	Z	AA	AB	AC
7	10.9%	$1,000	16		17	14	3		RYVYX	20.3%	#N/A
8	12.2%	$2,500	9		10	8	2		RMQHX	19.8%	1
9	10.9%	$2,500	15		16	13	3
10	10.0%	$2,500	20		21	18	3
11	9.0%	$2,500	23		24	19	5
12	10.0%	$2,500	19		20	17	3
13	10.1%	$3,000	18		19	16	3
14		$2,500	33		33	8.47E-22	33	2x
15	19.8%	$2,500	1	11.6%	33	1.69E-21	33	2x
ALL

Cell Formulas
Range	Formula
T7:T15	T7	=VLOOKUP(A7,'Funds 1'!$A$3:$F$12,6,0)
U7:U15	U7	=SUMPRODUCT((S7<=$S$4:$S$36)/COUNTIF($S$4:$S$36,$S$4:$S$36))
W7:W15	W7	=IF($U7=0,0,MAX($U7,$W7))
X7:X15	X7	=IF($X7=0,$U7,MEDIAN($X7,$U7,0))
Y7:Y15	Y7	=W7-X7
AB7:AB8	AB7	=$R14/$B14
AC7:AC8	AC7	=RANK(AB7,$S$4:$S$36)
S15,S10:S13,S7:S8	S7	=$R7/$B7
S9	S9	=($Q9-$B9)/$B9
V15	V15	=AVERAGE(S7:S15)

 

[Peter_SSs]

I'm not sure that I understood any of that.

In post #3, AC7 gives an error because the number it is trying to rank (20.3%) does not appear in column S. As I explained before RANK will only work if the number it is trying to rank is actually one of the numbers in the list.
AC8 works because the number it is trying to rank (19.8%) is in the list of numbers in column S (in S15)

 

[gtd526]

I'm not sure that I understood any of that.

In post #3, AC7 gives an error because the number it is trying to rank (20.3%) does not appear in column S. As I explained before RANK will only work if the number it is trying to rank is actually one of the numbers in the list.
AC8 works because the number it is trying to rank (19.8%) is in the list of numbers in column S (in S15)

That's what I figured, its not part of "the list".
Thank you.