Help to fully understand this formula

easybpw · Oct 23, 2003

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

fairwinds · Oct 23, 2003

Hi,
Thi first part gives you the rank within the list. However if there are more than one of that specific entrys they will all have the same rank. The second part counts the number of equal entries following in the list. That way all entries will get a unique rank.

easybpw · Oct 23, 2003

Ok I think I understand some. Without the COUNTIF then everyone has the same rank. Right? What does the -1 at the end of the formula do?

Bill

fairwinds · Oct 23, 2003

Wthout the -1; If rank is 1 and count is 1 result would be 2; it would rank from 2 and up...

easybpw · Oct 23, 2003

Ok I think I got it, for the most part. Thanks for the help!

Bill

Aladin Akyurek · Oct 23, 2003

easybpw said:
Ok I think I got it, for the most part. Thanks for the help!

Bill

There is a post of mine out there, which describes RANK in terms of COUNTIF. I couldn't find it myself, maybe you can.

easybpw · Oct 23, 2003

Aladin,

I think I found it, thanks! You have come through for me when needed!

Bill

easybpw · Oct 24, 2003

Hi again. A follow up to this post of mine. Below is a look at a simplified spreadsheet. I did it for this forum. The original is too big.

Book1

A

B

C

D

E

1

Rank

2

blue

10

7

3

orange

18

5

4

green

20

3

5

yellow

20

4

6

brown

12

6

7

purple

26

1

8

pink

22

2

9

10

Top5

5

11

12

13

14

15

Sheet1

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?

Any help is always appreciated!

Bill

Aladin Akyurek · Oct 24, 2003

easybpw said:
...
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? ...

Top5.xls

A

B

C

D

E

1

X

Y

Rank

2

blue

10

7

3

orange

18

5

4

green

20

3

5

yellow

20

4

6

brown

12

6

7

purple

26

1

8

pink

22

2

9

10

Top5

Y

5

11

12

13

14

15

16

17

18

Sheet2

Formulas...

D2:

=RANK(B2,$B$2:$B$8)+COUNTIF(B2:$B$2,B2)-1 [ no change ]

A11:

=IF(ROW()-ROW($A$11)+1<=$E$10+COUNT($B$11:B11)-SUMPRODUCT(($B$11:B11<>"")/COUNTIF($B$11:B11,$B$11:B11&"")),INDEX($A$2:$A$8,MATCH(ROW()-ROW($A$11)+1,$D$2:$D$8,0)),"")

Augmented for extending "Top 5" in case of ties in the Top N list.

B11:

=IF(ROW()-ROW($B$11)+1<=$E$10+COUNT($B$10:B10)-(SUMPRODUCT(($B$10:B10<>"")/COUNTIF($B$10:B10,$B$10:B10&""))-1),INDEX($B$2:$B$8,MATCH(ROW()-ROW($B$11)+1,$D$2:$D$8,0)),"")

Augmented for extending "Top 5" in case of ties in the Top N list.

easybpw · Oct 25, 2003

Aladin,

Thanks for the formulas. They seem very complex. I inserted them and things worked ok but when I copied down some data did not work. I do not know why as it worked the other way. Actually Column C copied correctly so I am very confused. Below is a small portion of the worksheet. Can you help me out?

Bill

EFS Football Scores 2003.xls