#### mphansen

##### New Member
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
ABCDEFGH
1
2Jul-02Aug-02Sep-02
3PerformancePerformancePerformance
4
5
61Fund18.6%4.3%14.3%4.3%9.4%4.3%
72Fund27.8%1.0%13.1%1.0%8.6%1.0%
84Fund37.8%-4.0%11.9%-4.0%7.8%-4.0%
95Fund47.3%1.2%11.0%1.2%7.2%1.2%
106Fund56.4%4.0%10.7%4.0%7.0%4.0%
117Fund68.3%-3.0%10.3%-3.0%9.1%-3.0%
1210Fund70.0%4.0%1.2%4.0%6.0%4.0%
138Fund80.0%1.0%8.6%1.0%5.6%1.0%
14 Fund9
1511Fund101.2%1.0%1.2%1.0%1.0%
169Fund114.5%9.0%7.4%9.0%4.9%9.0%
17
18
19
20CASH8.0%12.1%13.1%
EO2

### Excel Facts

Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
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...
ABCDEFGHI
16
219Jul-02Aug-02Sep-02
36PerformancePerformancePerformance
49
5Ranking: Aug-02
60.1431Fund18.6%4.3%14.3%4.3%9.4%4.3%
70.1312Fund27.8%1.0%13.1%1.0%8.6%1.0%
80.1193Fund37.8%-4.0%11.9%-4.0%7.8%-4.0%
90.114Fund47.3%1.2%11.0%1.2%7.2%1.2%
100.1075Fund56.4%4.0%10.7%4.0%7.0%4.0%
110.1036Fund68.3%-3.0%10.3%-3.0%9.1%-3.0%
120.0129Fund70.0%4.0%1.2%4.0%6.0%4.0%
130.0867Fund80.0%1.0%8.6%1.0%5.6%1.0%
140 Fund9
150.01210Fund101.2%1.0%1.2%1.0%1.0%
160.0748Fund114.5%9.0%7.4%9.0%4.9%9.0%
17
18
19
20CASH8.0%12.1%13.1%
EO2

This message was edited by Aladin Akyurek on 2002-08-23 07:13

Replies
1
Views
344
Replies
11
Views
896
Replies
4
Views
382
Replies
0
Views
209
Replies
3
Views
169

1,196,081
Messages
6,013,318
Members
441,760
Latest member
Sharina

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back