Rank formulas a bit modified

[radian89]

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

 

[alpadem]

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

 

[radian89]

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]

Re: HELP on Rank formulas a bit modified

my goal is only (1 to 4).

Hi, here is an option that you can try:

Excel 2013/2016
	C	D
4	Data	Rank
5	92	2
6	69	3
7	92	2
8	96	1
9	51	4
Sheet1

Cell Formulas
Range	Formula
D5	{=SUM(0+(FREQUENCY(IF($C$5:$C$9>C5,$C$5:$C$9),$C$5:$C$9)>0))+1}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Phuoc]

Re: HELP on Rank formulas a bit modified

Or try

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

 

[radian89]

Re: HELP on Rank formulas a bit modified

Or try

=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]

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.

 

[radian89]

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]

Re: HELP on Rank formulas a bit modified

Or try

=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