rank problem

B

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
 
B

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
 
B

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
 
B

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Solution to tricky problem of showing exactly Top N rows in Pivottable 0
ranking question 2
Tricky one 1
Rank Formula 1
Ties using Rank Function 3
Scoring Rank with Tie Breaker 3
If 3 If's, then sum 13
Rank and Sort 2

Top