Issue with Rank Count Formula

[Doofus1]

Hi Folks,
I have attached an excel 2010 spreadsheet to show the issue.
I was shown a formula to rank a series of numbers =RANK(A2,$A$2:$A$19775,0).
However, the ranking figure that get's generated, appears to be counting the cells or something like that.

This is all hurting my pretty little head.

Any helpful suggestions (regarding the fixing of the formula) would be greatly appreciated.

Thank you

[Ace_XL]

Try..

=SUMPRODUCT((A$3:A$19776>A3)/COUNTIF(A$3:A$19776,A$3:A$19776&""))+1

Will work even if your value are not necessarily in descending order

[Doofus1]

Hi Ace_XL,
I have entered the formula, however, it appears to be taking a really long time to produce the result.
Do you know of any reason why this may be so ?
Thank you

[daddylonglegs]

Hello Doofus1,

RANK function is working OK, that's the most common ranking system - if you have 3 people tied for 1st the next person is 4th not 2nd.

If you want to rank "without gaps" and your data in column A is in descending order like here then try this:

Put a 1 in D3 and then use this formula in D4 copied down

=D3+(A4<>A3)

That should give you the ranks you want

[Doofus1]

Hi Daddylonglegs,
This works. However, is there a way to calculate the rankings if the numbers are not sequentially organized.
Also is there a way to rank these numbers is they are related to individuals ?
My original post was related to this issue. Re: Giving scores a numeric rank
Thank you

[daddylonglegs]

.....Also is there a way to rank these numbers is they are related to individuals ?

I don't know what that means - can you give a small example?

[Doofus1]

Hi Daddylonglegs,
Sorry. it was a typo - should be "if" instead of "is".
So here's how my problem started.
I needed to rank a series of test scores for a number of students.
Got an answer from one of the experts on the forum. I have attached the Excel 2010 spreadsheet
The formula worked fine. However, when i tried to copy it to the rest of cells on the original document ( about 45,000 cells on the column) the document seems to hang and not go anywhere.
Any suggestions for what i am doing wrong or a simpler formula that would take less memory?

[micope21]

Hi

I have found a way for 45000 row as Sum or Sumproduct do take a long time and slow down?

Only way to help go faster is use 1 helper

Helper
C3

=IF(ISNUMBER(MATCH(A3,$A$2:A2,0)),"",RANK(A3,$A$3:$A$45000))

copy down

D3

=IF(ISNUMBER(MATCH(A3,$A$2:A2,0)),VLOOKUP(A3,$A$2:D2,3,0),RANK(C3,$C$3:$C$45000,1))

copy down

See the file!

Regard

[Doofus1]

Hi Micope21,
Thank you. Sorry I didn't respond earlier.
I have another question though.
I have attached another Excel spreadsheet for you to look at.
I this excel spreadsheet, I have a group of students - names in coulmn A
Their test scores are in coulm B.
In this example the Names and Scores for each student have been sorted - Alphabetically and Highest to Lowest.
However, in reality, they are in the order in which they took the test and by subject (the columns are not included).
I want the same type of formula you explained but this time, for each student - so that that i can rank each students scores.
The formula I was originally shown, calculated too slowly.
The one you outlined above worked well enough but i need a fast efficient formula to rank each students grades.
Any suggestions would be much appreciated.
Thank you

[micope21]

Hi Doofus1

Give me other hard work again lol!

I notice Mr Sktneer had giving you the array formula using Sum if and countif?
Yes that will slow you right down and take a long time if you have 45,000 rows?
I believe that the problem you having at the moment!

I always like to give a formula without helper?
In your case for 45,000 rows it can't be done! (Again anything can be possible)
Only way it use 1 helper!

Good news, I solved it with 45,000 rows faster.

Same again with 1 helper!

C3

=1/SUM(IF(($A$3:$A$45000=A3)*($B$3:$B$45000=B3),1,0))

This is array formula! Press same time Ctrl+Shift+Enter, NOT ENTER. Copy down.

Rank

D3

=SUM(IF(($B$3:$B$45000>B3)*($A$3:$A$45000=A3),$C$3:$C$45000,0))+1

Array formula Ctrl+Shift+Enter then copy down.

See the file!

It won't be faster than the first file I done!
It will be faster than Sktneer file!
Thank you for Reputation!

Regard

[Doofus1]

Hi Micope21,
Yes. this works.
Still kinda slow - but like you said, faster than the previous calculation.
Thank you. It is all really appreciated.
Thank you