# Rank formulas a bit modified

##### Board Regular
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

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

##### New Member
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:

##### Board Regular
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
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>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

#### Phuoc

##### Board Regular

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``

##### Board Regular
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

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:

##### Board Regular
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

##### Board Regular
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

Replies
4
Views
192
Replies
3
Views
71
Replies
8
Views
145
Replies
3
Views
839
Replies
6
Views
90