Rank formulas a bit modified

radian89

Board Regular
Joined
Nov 12, 2015
Messages
110
Hi,

i'd like to ask, since this is bit confusing, i don't know how to rank like this case. my goal is to keep the rank in order, not skip it even if there's duplicate.



thanks a lot for your help
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

alpadem

New Member
Joined
Oct 9, 2018
Messages
15
Re: HELP on Rank formulas a bit modified

Hi,

ın F5 cell = RANK.EQ(C5,$C$5:$C$9,0)+COUNTIF($C$5:C5,C5)-1 then copy down it gives you 1, 2, 3, 4 ,5
 
Last edited:

radian89

Board Regular
Joined
Nov 12, 2015
Messages
110
Re: HELP on Rank formulas a bit modified

Hi,

ın F5 cell = RANK.EQ(C5,$C$5:$C$9,0)+COUNTIF($C$5:C5,C5)-1 then copy down it gives you 1, 2, 3, 4 ,5

hi,

nope, it'll give a sequential rank (1 to 5), instead of let it duplicates, my goal is only (1 to 4).
 

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,430
Office Version
  1. 365
Platform
  1. Windows
Re: HELP on Rank formulas a bit modified

my goal is only (1 to 4).

Hi, here is an option that you can try:

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Data</td><td style=";">Rank</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">92</td><td style="text-align: right;background-color: #FFFF00;;">2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">69</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">92</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">96</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;">51</td><td style="text-align: right;;">4</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D5</th><td style="text-align:left">{=SUM(<font color="Blue">0+(<font color="Red">FREQUENCY(<font color="Green">IF(<font color="Purple">$C$5:$C$9>C5,$C$5:$C$9</font>),$C$5:$C$9</font>)>0</font>)</font>)+1}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 

Phuoc

Board Regular
Joined
Apr 29, 2016
Messages
209

ADVERTISEMENT

Re: HELP on Rank formulas a bit modified

Or try
Code:
=SUMPRODUCT(($C$5:$C$9>C5)/COUNTIF($C$5:$C$9,$C$5:$C$9))+1
 

radian89

Board Regular
Joined
Nov 12, 2015
Messages
110
Re: HELP on Rank formulas a bit modified

Or try
Code:
=SUMPRODUCT(($C$5:$C$9>C5)/COUNTIF($C$5:$C$9,$C$5:$C$9))+1


Hi Phuoc,

thanks so much with the formula given, it works very well, one more question, what if i try to ascending & descending? what to change?
 

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,430
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Re: HELP on Rank formulas a bit modified

one more question, what if i try to ascending & descending? what to change?

You would change the ">" symbol for "<".

For my suggestion, you would change the first ">" only.

FWIW, using FREQUENCY() for these unique conditional counts is known to be faster than the countif() alternative - whether you will notice the difference though probably comes down to how big your data set is and how many times you are invoking the formula.
 
Last edited:

radian89

Board Regular
Joined
Nov 12, 2015
Messages
110
Re: HELP on Rank formulas a bit modified

You would change the ">" symbol for "<".

For my suggestion, you would change the first ">" only.

FWIW, using FREQUENCY() for these unique conditional counts is known to be faster than the countif() alternative - whether you will notice the difference though probably comes down to how big your data set is and how many times you are invoking the formula.


ah yes, thanks for the advise. well noted, if use FREQUENCY(), it'll force excel to use array. but using countifs, it'll doesn't need to use array. since the data is small, i'll stick to the countifs, later if there's big data i'll try to compare it using frequency, thanks a lot for the advise
 

radian89

Board Regular
Joined
Nov 12, 2015
Messages
110
Re: HELP on Rank formulas a bit modified

Or try
Code:
=SUMPRODUCT(($C$5:$C$9>C5)/COUNTIF($C$5:$C$9,$C$5:$C$9))+1


Hi Phuoc,

Could you help me explain how's this logic of this formulas works, if you don't mind? I couldn't figure out myself ;P

thanks a lot
Adrian
 

Watch MrExcel Video

Forum statistics

Threads
1,109,521
Messages
5,529,325
Members
409,863
Latest member
stacy09
Top