Ranking multiple sets in a column

[jake133]

Hi All, this should be an easy one.. I'm using Excel 97 in Windows 2000 Professional.

I'm trying to rank my data (cfs) within each year (1932-2002) for which i have data.

I have 3 columns: Year (identified from column B, Date), Date and the "cfs" that look simply like this:


Year Date cfs
1932 1/1/1932 78
1932 1/2/1932 105
1932 1/3/1932 83
. .
. .
. .
2002 12/30/2002 85
2002 12/31/2002 96

I have 70 years of daily data (over 25,000 rows) of which I would like to rank the cfs within each year. So, I defined all the Year data as "year" and cfs data as "cfs" and used the below formula in column D:

=IF(year=A2,RANK(C2,cfs))

I also tried:

=RANK(C2,IF(year=A2,cfs))

In both cases Excel Ranks C2 in light of the entire dataset of 1932 to 2002 and not just year 1932. Of course, it works if I go in a define cfs as just the cfs in 1932 but i'd rather not go down that road. I'd like to have a single formula that can by copied down 25,000 rows..

Any thoughts - or if you can point me to an already-posted answer - I'd be appreciative. Thanks!

 

[IML]

If I understand you right, try
=SUMPRODUCT((C1>=$C$1:$C$5)*(A1=$A$1:$A$5))
where A1:C5 represents your whole data set. results on your data were
1
3
2
1
2

 

[jake133]

Thanks for your help..
It does rank a single year's worth of data separately from other years but I end up with different #'s than if I used the RANK feature. For example, If my 5 lowest values all 20, using the formula you provided it will rank them all as 5. Whereas with the RANK feature it will rank them as 365, 364, 363, 362, 361 or 0,1,2,3,4, and 5.
It is a step in the right direction in terms being able to distinguish between years.
BJ

 

[jake133]

On second look - it works great. I just need to subtract the answer from 366 and I get what I need. Multiple values are fine. Thanks very much. BJ

 

[IML]

You can also flip the sign to
=SUMPRODUCT((C1<=$C$1:$C$5)*(A1=$A$1:$A$5))

 

[jake133]

That's helpful. Thanks much. BAJ