Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
rank problem
Hi Bill!
Here's a sample file: (16 kb) http://cjoint.com/?gei6Z5jGpF Open the file. Goto the menu InsertNameDefine 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem with MS Community Newsgroups? | Excel Discussion (Misc queries) | |||
Rank with condition | Excel Discussion (Misc queries) | |||
Problem with Pasting Data | Setting up and Configuration of Excel | |||
strange problem with links updates in excel | Excel Discussion (Misc queries) | |||
Freeze Pane problem in shared workbooks | Excel Discussion (Misc queries) |