Ranking Dates and Grouping Together

S

scottymelloty

this is a sample of my data

15-01-06 xxxxx
15-01-06 xxxxx
15-01-06 xxxxx
20-01-06 xxxxx
20-01-06 xxxxx
20-01-06 xxxxx
27-01-06 xxxxx
27-01-06 xxxxx

i want to rank the dates so i can do some working outs from them , when
i rank the dates its starts off at no1 for all the 15-01-06 and then
goes to no4 for the 20-01-06 then no 7 for the 27-01-06 data ,

how can i rank them so it puts no1 for all 15-01 , then no2 for all
20-01 then no3 for 27-01 and so on

there will be about 150 entries with the same date every week and i
want it to count the whole group of dates as 1 rank instead of it
counting every date basically

or is there a better way , many thanks :)
 
B

Bernie Deitrick

Scott,

Use this array formula, entered using Ctrl-Shift-Enter:

=RANK(A2,$A$2:$A$11,TRUE)-(COUNTIF($A$2:$A$11,"<"&A2)-SUM((1/COUNTIF($A$2:$A$11,$A$2:$A$11))*($A$2:$A$11<A2)))

Adjust the ranges to suit.

HTH,
Bernie
MS Excel MVP
 
S

scottymelloty

Thanks i have tried this but i am having problems

when i tried this i just get everything returning as 1

my date data is in ranges from A2:A942 and i have changed this in the
formula , im not sure how to enter the data using shift+ctrl+enter ,
that dosnt seem to do anything, is that what i am doing wrong ?

many thanks
 
B

Bernie Deitrick

Scott,

Select the first cell with that formula, press F2 to enter edit mode, then
press and hold the Ctrl key, press and hold the Alt key and, with those two
keys still held down, press the Enter Key, similar to using Ctrl Alt Del to
reboot. Then the formula will be entered as an array formula. Copy down
the column to match your data, and all the cells won't return 1.

HTH,
Bernie
MS Excel MVP


"scottymelloty" <[email protected]>
wrote in message
news:[email protected]...
 
S

scottymelloty

Thanks, thats works a treat , a problem i seem ot have tho is that i
keeps crashing excel when i use it on my large database, excel freeze
altogether, no problems on a smaller sample of selections but ever
time crashes on the file i want to use it on as soon as i copy th
formula down it says calculating cells and then freezes and nothin
works, so i presume its the ammount of data im trying to rank , bu
thats another problem i guess.

thanks for your time and help :
 
Top