Ranking

gtd526

Active Member
Joined
Jul 30, 2013
Messages
334
Office Version
  1. 2019
Platform
  1. Windows
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)
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
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. :confused:

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
Joined
Jul 30, 2013
Messages
334
Office Version
  1. 2019
Platform
  1. Windows
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. :confused:

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
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jul 30, 2013
Messages
334
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,064
Messages
5,628,388
Members
416,315
Latest member
certainlyfrustrated

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top