# Ranking

#### gtd526

##### Active Member
Hello,
Im trying to Rank AB7 & AB8 using S4:S36.
AC7 = Rank
AC8 = Rank
There are NO merged cells.

Cell Formulas
RangeFormula
S4,S23:S24,S17:S21,S10:S13,S7:S8S4=\$R4/\$B4
T4,T17:T21T4=VLOOKUP(A4,'Funds 2'!\$A\$3:\$F\$8,6,0)
U4,U23:U36,U17:U21,U7:U13U4=SUMPRODUCT((S4<=\$S\$4:\$S\$36)/COUNTIF(\$S\$4:\$S\$36,\$S\$4:\$S\$36))
W23:W36,W17:W21,W7:W15,W4:W5W4=IF(\$U4=0,0,MAX(\$U4,\$W4))
X23:X36,X17:X21,X7:X13,X4:X5X4=IF(\$X4=0,\$U4,MEDIAN(\$X4,\$U4,0))
Y23:Y36,Y17:Y21,Y7:Y15,Y4:Y5Y4=W4-X4
V5V5=AVERAGE(S4:S5)
AC7AC7=RANK(AB7,S4:S36)
AB7:AB8AB7=\$R14/\$B14
S9,S25:S36S9=(\$Q9-\$B9)/\$B9
T7:T15T7=VLOOKUP(A7,'Funds 1'!\$A\$3:\$F\$12,6,0)
V15V15=AVERAGE(S7:S15)
V21V21=AVERAGE(S17:S21)
T23:T36T23=VLOOKUP(A23,'T Rowe Price'!\$A\$2:\$F\$15,6,0)
V36V36=AVERAGE(S23:S36)

### Excel Facts

To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

#### Peter_SSs

##### MrExcel MVP, Moderator
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

##### Active Member
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.

Cell Formulas
RangeFormula
T7:T15T7=VLOOKUP(A7,'Funds 1'!\$A\$3:\$F\$12,6,0)
U7:U15U7=SUMPRODUCT((S7<=\$S\$4:\$S\$36)/COUNTIF(\$S\$4:\$S\$36,\$S\$4:\$S\$36))
W7:W15W7=IF(\$U7=0,0,MAX(\$U7,\$W7))
X7:X15X7=IF(\$X7=0,\$U7,MEDIAN(\$X7,\$U7,0))
Y7:Y15Y7=W7-X7
AB7:AB8AB7=\$R14/\$B14
AC7:AC8AC7=RANK(AB7,\$S\$4:\$S\$36)
S15,S10:S13,S7:S8S7=\$R7/\$B7
S9S9=(\$Q9-\$B9)/\$B9
V15V15=AVERAGE(S7:S15)

#### Peter_SSs

##### MrExcel MVP, Moderator
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

##### Active Member
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.

Replies
1
Views
200
Replies
19
Views
332
Replies
16
Views
351
Replies
6
Views
79
Replies
2
Views
45

1,127,431
Messages
5,624,747
Members
416,046
Latest member
Elliottj2121

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back