rank problem

[bill gras]

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

 

[Biff]

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 Insert>Name>Define
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]

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

 

[Biff]

Hi Bill!

Here's a sample file: (16 kb)

http://cjoint.com/?gei6Z5jGpF

Open the file.

Goto the menu Insert>Name>Define

There is only one name listed, Range.

Select Range and the formula will appear in the box at the bottom of the
user form. Be careful with this dialog box. If you select the formula and
start using the arrow keys it can change references. Before you start
scrolling the formula make sure you first hit function key F2. This prevents
the references from changing. This dialog box is one of the worst I've
encountered. It's sooooo small and a real PITA if you don't hit F2 first.

Biff