Dynamically adjusting rank

mphansen · Aug 22, 2002

Hi guys, I'm trying to rank values in a sheet, and would like the rank to adjust to the appropriate date column that I'm trying to rank. At the same time I'm trying to narrow the rank range.

In otherwords, Rank the column to the left of the current month that we are in (August), and begin rank below the months but above the cash entry.

I've got the column:
MATCH(DATE(YEAR(TODAY()),MONTH(TODAY()),1),2:2,0)-1

I've got the last boundary row:
MATCH(B20,B:B,0)

And a rudimentary rank formula:
IF(OR(E6=0,E6=""),"",RANK(E6,$E:$E,0)+COUNTIF($E$6:$E6,E6)-1)

Now how I can put the dynamic rank together ?
See HTML for info
Any insight appreciated,
Mark

Book4

A

B

C

D

E

F

G

H

1

2

Jul-02

Aug-02

Sep-02

3

Performance

4

5

6

1

Fund1

8.6%

4.3%

14.3%

4.3%

9.4%

4.3%

7

2

Fund2

7.8%

1.0%

13.1%

1.0%

8.6%

1.0%

8

4

Fund3

7.8%

-4.0%

11.9%

-4.0%

7.8%

-4.0%

9

5

Fund4

7.3%

1.2%

11.0%

1.2%

7.2%

1.2%

10

6

Fund5

6.4%

4.0%

10.7%

4.0%

7.0%

4.0%

11

7

Fund6

8.3%

-3.0%

10.3%

-3.0%

9.1%

-3.0%

12

10

Fund7

0.0%

4.0%

1.2%

4.0%

6.0%

4.0%

13

8

Fund8

0.0%

1.0%

8.6%

1.0%

5.6%

1.0%

14

Fund9

15

11

Fund10

1.2%

1.0%

1.2%

1.0%

16

9

Fund11

4.5%

9.0%

7.4%

9.0%

4.9%

9.0%

17

18

19

20

CASH

8.0%

12.1%

13.1%

EO2

Brian from Maui · Aug 22, 2002

Mark,

This is beyond me, but this might help!

http://www.mrexcel.com/board/viewtopic.php?mode=viewtopic&topic=1697&forum=2&start=10

Aladin Akyurek · Aug 23, 2002

Brian has digged up a reference to RankIf (Conditional Rank)... by some Mauian magic.

I can't more often than not find such things back myself.

You have however a different issue here. How do you manage to cook up such things, Mark, which are difficult to manage? :wink:

I'll assume the layout shown in the figure you posted.

( 1.) Activate the WB housing the worksheet EO2.
( 2.) Activate Insert|Name|Define.
( 3.) Enter BigNum as name in the Names in Workbook box.
( 4.) Enter the following in the Refers to box:

9.99999999999999E+307

( 5.) Click OK.
( 6.) Activate the worksheet EO2.
( 7.) Insert a new column before the current column A.
( 8.) In A1 enter:

=MATCH(DATE(YEAR(TODAY()),MONTH(TODAY()),1),2:2,0)-1

Note. This formula can only work if the date entries in row 2 all have 1 as the day value, that is, 7/1/2002, 8/1/2002, 9/1/2002, etc.

( 9.) In A2 enter:

=MATCH("CASH",C:C,0)

which requires that the label CASH is there where it's expected to be.

(10.) In A3 enter:

=CELL("Row",C6)

(11.) In A4 enter:

=MATCH(BigNum,2:2)

(12.) Activate Insert|Name|Define.
(13.) Enter PerTable (from performance table) as name in the Names in Workbook box.
(14.) Enter the following dynamic formula in the Refers to box:

=OFFSET('EO2'!$C$6,0,0,'EO2'!$A$2-'EO2'!$A$3,'EO2'!$A$4)

(15.) Click OK.
(16.) In A6 enter and copy down as far as needed:

=INDEX(PerTable,ROW()-$A$3+1,$A$1-2)

Note. I believe the copying down of the formula in A6 can (even: should) be done by a VBA code automatically based on a change event using the parameter value computed in A2!

(17.) In B5 enter:

="Ranking: "&CHAR(10)&TEXT(INDEX(2:2,A1+1),"mmm-yy")

which is just cosmetics. Check Wrap text using Format|Cells wrt B5.

(18.) In B6 enter the essential formula and copy down as far as needed:

=IF(A6,RANK(A6,INDEX(PerTable,0,$A$1-2))+COUNTIF($A$6:$A6,A6)-1,"")

Note. The VBA code which would do the copying down of the formula in A6 should also copy down the RANK formula in B6.

(19.) Finally, hide column A if so desired.

See the HTML figure...

aaDynamicRank mphansen.xls