Rank formulas a bit modified

radian89

Board Regular
Joined
Nov 12, 2015
Messages
113
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.

zmbd6e.jpg


thanks a lot for your help
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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:
Upvote 0
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).
 
Upvote 0
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
CD
4DataRank
5922
6693
7922
8961
9514
Sheet1
Cell Formulas
RangeFormula
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.
 
Upvote 0
Re: HELP on Rank formulas a bit modified

Or try
PHP:
=SUMPRODUCT(($C$5:$C$9>C5)/COUNTIF($C$5:$C$9,$C$5:$C$9))+1
 
Upvote 0
Re: HELP on Rank formulas a bit modified

Or try
PHP:
=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?
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
Re: HELP on Rank formulas a bit modified

Or try
PHP:
=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
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,954
Members
448,535
Latest member
alrossman

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
Back
Top