Hi Biff
Thank you for your time and effort
Can you tell me which column and cell to put " create this named formula"
in , ect.

Thanks
bill gras
--
bill gras


"Biff" wrote:

Hi!

This was quite a challenge!

If your range is H1:H20 insert a new row 1 and leave H1 EMPTY. So, now the
range of data is H2:H21.

Create this named formula:

Goto InsertNameDefine
Name: Range
Refers to:

=INDIRECT("H"&SMALL(IF(H$1:H$25="",ROW(H$1:H$25)), COUNTBLANK(H$1:H2))&":H"&MATCH(TRUE,H2:H$25="",0)+ ROWS($1:1))

You need an EMPTY cell at the end of the range so you'll notice in the named
formula above I'm using a range that ends in H25.

Enter this formula in M2 as an array using the key combination of
CTRL,SHIFT,ENTER:

=IF(H2<1,"",SUM(IF(H2<Range,1/COUNTIF(Range,Range)))+1)

Copy down as needed.

Biff

"bill gras" wrote in message
...
I have in column H random sequences of rows with numbers , each sequence
of
rows is seperated by an empty row. in column M I have a formula
=RANK(H1,H1:H11) copied down , in column N i have a formula :
=IF(M1<=15,CHOOSE(M1,100,75,56,42,34,24,18,13,10,8 ,6,4,3,2,1)) copied down
.
each new sequence has to start and stop between the empty rows .

H M N needs to be M N
100 1 100 1 100
100 1 100 1 100
100 1 100 1 100
98 4 42 2 75
92 5 34 3 56
92 5 34 3 56
90 7 18 4 42
87 8 13 5 34
81 9 10 6 24
79 10 8 7 18
66 11 6 8 13
empty row empty row
100 1 100 1 100
98 2 75 2 75
77 3 56 3 56
63 4 42 4 42
0
0
empty rows empty rows

If the numbers in column H are duplicates the rank order is the same ,
but
M4 needs to be the 2nd rank order , not the 4th rank order and so on .
also
when there are zerow's in rows of column H there should be blank cells
in
the corresponding cells in columns M and N as above

Can this be done?
Thanks in advance

regards bill gras

















--
bill gras