Help to fully understand this formula

[easybpw]

Everyone, thanks for all the help. I have another question. I have been trying to understand this formula I have been given but I can not understand all the parts.The formula does work wonderfuly. I use it to rank certain cells. Here it is:

=RANK(AC6,$AC$6:$AC$71)+COUNTIF($AC$6:AC6,AC6)-1

I understand the rank portion but....would someone kindly tell me in simple terms what the COUNTIF part does? That is where I get lost. Again I know the formula works as I want it but I don't understand it and a friend of mine is asking me what this part of the formula is for.

Thanks again.

Bill

 

[Aladin Akyurek]

...What I was wondering is....how can I get the ranking to include the top 5? I mean that is what I asked for but because of the tie (green and yellow tied at 20) it really is only giving me the top 4. I know if I change the 5 to 6 in column E10 that would do it but lets say there isn't a tie in this instance then I would get the top 6 and I only want the top 5. I think the countif function of the formula is determining the ties, right?...

Bill,

Although I already replied to this post with a proposal, that isn't good enough. I should also add that, as far as I know, you have been the first to raise such an important issue: A self-correcting or dynamically changing 'top N parameter'.

What follows corrects my initial post on the subject/issue and is faster performancewise.

Data,including ranking...

Top5 v2.xls
	A	B	C	D
1	X	Y		Rank
2	blue	22		4
3	orange	40		1
4	green	20		7
5	yellow	22		5
6	brown	26		2
7	purple	26		3
8	pink	22		6
Data

The formula for ranking in D2 is:

=RANK(B2,$B$2:$B$8)+COUNTIF(B2:$B$2,B2)-1

TopN

E1 houses the parameter Top N, currently set to 4.

Top5 v2.xls
	A	B	C	D	E
1	Top5	Y		2	4
2	orange	40
3	brown	26
4	purple	26
5	blue	22
6	yellow	22
7	pink	22
8
9
10
TopN

The formulas...

D1:

=MAX(IF(INDEX(Data!B2:B8,MATCH(E1,Data!D2:D8,0))=Data!B2:B8,Data!D2:D8))-E1

which must be confirmed with control+shift+enter instead of just with enter.

This formula takes care of dynamic adjusting that is needed when Nth top value appears to be a tie.

A2:

=IF(B2<>"",INDEX(Data!$A$2:$A$8,MATCH(ROW()-ROW($A$2)+1,Data!$D$2:$D$8,0)),"")

B2:

=IF(ROW()-ROW($B$2)+1<=$E$1+$D$1,INDEX(Data!$B$2:$B$8,MATCH(ROW()-ROW($B$2)+1,Data!$D$2:$D$8,0)),"")

Note that this refers to E1 as well as D1.

Select A2:D2 & copy down to a sufficient numbers of rows.

 

[easybpw]

Aladin,

Once again thanks for your help and great gift of knowledge. I will study these new formulas and use them appropriately.

Thanks again!

Bill

 

[mianmazher]

Sir Aladin,

Plz I need to know that when I write your formula for Top 4 instead of showing TOP 4 its shows Top 5

I have also searched the forum and founf that in many posts

instead of the required top N its always giving N+1.


Sir Aladin Ur help (explanation) will be appreciated.


Regards

 

[Aladin Akyurek]

...
Plz I need to know that when I write your formula for Top 4 instead of showing TOP 4 its shows Top 5

I have also searched the forum and founf that in many posts

This thread is the one where I designed a formula system for solving the Top N class of problems. Immensely indebted to Bill for his judicious demand to also deliver the ties of the Nth value.

instead of the required top N its always giving N+1...

Not N+1, it's rather N+x, where x >= 0. This means: When there is no multiple instances of the Nth value, you just get N.

 

[mianmazher]

Sir Aladin

I am not getting.

Instead of top 5 I get 6 Values

when dealing with bottom 5 It shows the five values.

Plz tell why it does not show top 5 and shows bottom 5.


Regards

 

[just_jon]

Are there any ties within the Top 5?

 

[mianmazher]

Dear Sir John,

If I am right u mean to say that if there is a tie then it will show Top 6 instead of Top 5 I

If not a tie it will show Top 5.


Regards

 

[just_jon]

Yes; that's the point, that the formula be dynamic in that the top N values show, regardless of how many scores that is.

EDIT -- if you just want the top N period, just use RANK/LARGE/SMALL with 1 thru N.

 

[mianmazher]

Thanx Sir John ,


I got it.



Regards