+ Reply to Thread
Results 1 to 4 of 4

rank problem

  1. #1
    bill gras
    Guest

    rank problem

    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

  2. #2
    Biff
    Guest

    Re: rank problem

    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" <[email protected]> wrote in message
    news:[email protected]...
    >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




  3. #3
    bill gras
    Guest

    Re: rank problem

    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 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" <[email protected]> wrote in message
    > news:[email protected]...
    > >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

    >
    >
    >


  4. #4
    Biff
    Guest

    Re: rank problem

    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

    "bill gras" <[email protected]> wrote in message
    news:[email protected]...
    > 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 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" <[email protected]> wrote in message
    >> news:[email protected]...
    >> >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

    >>
    >>
    >>




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1