Frequency function to correct ranking instead of countif

Unresolved Cross post from :

http://www.mrexcel.com/forum/excel-q...d-countif.html

Hello,
I have been using countif to rank numbers across +20 000 rows (+100 000 rows before data cleanup). Needless to say, this countif function is slowing down my model to a point it even crashes my excel. To fix this, I am trying to find a way to use the frequency function.

Below is an example of the desired results using countif in column C :

In C2, I use the classic : =COUNTIF(B$2:B2,B2)-1, which I copy down to the last row. (sorry but the paste and format functionalities on this site are not helping)

A B C D
1 values rank rank adjustment real rank
2 100 7 0 7
3 200 6 0 6
4 300 5 0 5
5 420 2 0 2
6 500 1 0 1
7 420 2 1 3
8 310 4 0 4



Now for the frequency version, the following forumula is wrong, but I understand it needs to look a little something like this, in C2 :

{=SUM(IF(FREQUENCY(IF(B$2:B2<>"",IF(B$2:B2=B2,MATCH("~"&B$2:B2,B$2:B2&"",0))),ROW(B$2:B2)-ROW(B$2)+1),1))-1}


Tons of thanks in advance for your kind help !

Scott

im not sure why you need frequency()
you can get that result by
=RANK(A2,$A$2:$A$25000,0)+COUNTIF($A$2:A2,A2)-1

it wont slow it as much as any array formula you use

cross post at
http://www.mrexcel.com/forum/excel-q...d-countif.html

Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

=RANK(A2,$A$2:$A$50)+INDEX(FREQUENCY($A$2:A2,$A$2:A2),MATCH(A2,A$2:A2,0))-1
gives same result as
=RANK(A2,$A$2:$A$50)+COUNTIF($A$2:A2,A2)-1
mind you on 25000 rows it took about 3 times longer to calculate

The YouTube vid that you talk about http://www.youtube.com/watch?v=_cWg850PMys is the fastest for large spread sheets according to the presentation.

The SUMIF, COUNTIF AND AVERAGEIF functions are much faster than their predecessors.

you agreed to the rules before you posted if you cant be bothered to abide by them i cant be bothered to help

Scottlarock, might I suggest slightly less sarcasm in your posts here? You are new to the forum, and perhaps did not pay quite as much attention to the rules as you might have when you read them?

ALL members here offer their time and effort free of charge, we are all here to help each other, and politeness is a requirement here. Perhaps adherance to the old adage...if you cant say anything nice, dont say anything...would be worth following

As far as I can see, 2 very experienced members have offered some very good suggestions and advice to you, most members would take the time to test them, before rejecting them in favor of what some-one else "said".

Thanks to taking the time to read this )

INDEX(FREQUENCY($A$2:A2,$A$2:A2),MATCH(A2,A$2:A2,0))-1 does the same thing no array entered needed
but it is slower ,even making array formulas is not always a way for speed
rank() itself is slow on big data sets then add countif it gets slower still
now if there is away to combine both to both rank and countif to produce unique rank using frequency instead i admit i dont know it
but this i dont understand

gain credits to get certified.

this forum has nothing to do with MS and the only certification i'll get is when i turn down a free drink for no reason
the quickest way i can see is to do the rank as normal
then use countif()-1 on the results

1st, there was no threat made to you, Martin posted a copy of the rule that you failed to adhere to. Thats what the rule said, and thats all Martin was pointing out to you - something that you should have read when you joined.

2nd, if you think that giving me a negative rep, with the comment "Complete loser, intervenes with no purpose but to play captain america." attached will earn you more respect here, or that it would offend or bother me in any way, good luck with that

If I had to make a choice between taking the advice of some video posted on youtube (which literally anyone can do) and following the proven advice of most of the members here...hey, but thats my choice to make, right?