If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Formula required.
I have rows of figures over a number of columns, starting at column A, with totals for each row in column K. I then want to allocate values from 1 to 8 to each of the rows, in column L, with 8 to the highest total and 1 to the lowest total, if 2 rows are the same total they each receive half of the combined figures, as per the examples below:- (This is a very simple example of what I use, in reality there are some 24 columns and 16 rows, where the values are from 1 to 16 rather than 1 to 8) ABCDE FG H I J K L Row 1 = 5 6 6 2 4 2 4 3 2 6 = 40 -- 8 Row 2 = 5 3 3 6 0 2 0 3 2 4 = 28 -- 2 Row 3 = 1 0 0 4 2 6 6 0 6 4 = 29 -- 3.5 Row 4 = 1 3 3 0 6 2 2 6 2 6 = 31 -- 5.5 Row 5 = 1 0 0 4 2 4 2 3 4 0 = 20 -- 1 Row 6 = 1 3 3 0 6 4 6 3 4 2 = 32 -- 7 Row 7 = 5 6 6 2 4 0 0 6 0 2 = 31 -- 5.5 Row 8 - 5 3 3 6 0 4 4 0 4 0 = 29 -- 3.5 What I require is a formula which will automatically insert the appropriate value, 1 to 8 in the example above. I would be most grateful for any suggestions. -- JohnD |
#2
|
|||
|
|||
Formula required.
=IF(COUNTIF(A$1:A$8,A1)=1,RANK(A1,A$1:A$8,1),RANK( A1,A$1:A$8,1)+0.5)
"sherbrooke" wrote: I have rows of figures over a number of columns, starting at column A, with totals for each row in column K. I then want to allocate values from 1 to 8 to each of the rows, in column L, with 8 to the highest total and 1 to the lowest total, if 2 rows are the same total they each receive half of the combined figures, as per the examples below:- (This is a very simple example of what I use, in reality there are some 24 columns and 16 rows, where the values are from 1 to 16 rather than 1 to 8) ABCDE FG H I J K L Row 1 = 5 6 6 2 4 2 4 3 2 6 = 40 -- 8 Row 2 = 5 3 3 6 0 2 0 3 2 4 = 28 -- 2 Row 3 = 1 0 0 4 2 6 6 0 6 4 = 29 -- 3.5 Row 4 = 1 3 3 0 6 2 2 6 2 6 = 31 -- 5.5 Row 5 = 1 0 0 4 2 4 2 3 4 0 = 20 -- 1 Row 6 = 1 3 3 0 6 4 6 3 4 2 = 32 -- 7 Row 7 = 5 6 6 2 4 0 0 6 0 2 = 31 -- 5.5 Row 8 - 5 3 3 6 0 4 4 0 4 0 = 29 -- 3.5 What I require is a formula which will automatically insert the appropriate value, 1 to 8 in the example above. I would be most grateful for any suggestions. -- JohnD |
#3
|
|||
|
|||
Formula required.
Try this formula...
=(2*RANK(K1,K$1:K$8,1)+(COUNTIF(K$1:K$8,K1)1))/2 changing the ranges to match your actual conditions, of course. -- Rick (MVP - Excel) "sherbrooke" wrote in message ... I have rows of figures over a number of columns, starting at column A, with totals for each row in column K. I then want to allocate values from 1 to 8 to each of the rows, in column L, with 8 to the highest total and 1 to the lowest total, if 2 rows are the same total they each receive half of the combined figures, as per the examples below:- (This is a very simple example of what I use, in reality there are some 24 columns and 16 rows, where the values are from 1 to 16 rather than 1 to 8) ABCDE FG H I J K L Row 1 = 5 6 6 2 4 2 4 3 2 6 = 40 -- 8 Row 2 = 5 3 3 6 0 2 0 3 2 4 = 28 -- 2 Row 3 = 1 0 0 4 2 6 6 0 6 4 = 29 -- 3.5 Row 4 = 1 3 3 0 6 2 2 6 2 6 = 31 -- 5.5 Row 5 = 1 0 0 4 2 4 2 3 4 0 = 20 -- 1 Row 6 = 1 3 3 0 6 4 6 3 4 2 = 32 -- 7 Row 7 = 5 6 6 2 4 0 0 6 0 2 = 31 -- 5.5 Row 8 - 5 3 3 6 0 4 4 0 4 0 = 29 -- 3.5 What I require is a formula which will automatically insert the appropriate value, 1 to 8 in the example above. I would be most grateful for any suggestions. -- JohnD |
#4
|
|||
|
|||
Formula required.
Actually, this formula is a little simpler...
=RANK(K2,K$1:K$8,1)+(COUNTIF(K$1:K$8,K2)1)/2 -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Try this formula... =(2*RANK(K1,K$1:K$8,1)+(COUNTIF(K$1:K$8,K1)1))/2 changing the ranges to match your actual conditions, of course. -- Rick (MVP - Excel) "sherbrooke" wrote in message ... I have rows of figures over a number of columns, starting at column A, with totals for each row in column K. I then want to allocate values from 1 to 8 to each of the rows, in column L, with 8 to the highest total and 1 to the lowest total, if 2 rows are the same total they each receive half of the combined figures, as per the examples below:- (This is a very simple example of what I use, in reality there are some 24 columns and 16 rows, where the values are from 1 to 16 rather than 1 to 8) ABCDE FG H I J K L Row 1 = 5 6 6 2 4 2 4 3 2 6 = 40 -- 8 Row 2 = 5 3 3 6 0 2 0 3 2 4 = 28 -- 2 Row 3 = 1 0 0 4 2 6 6 0 6 4 = 29 -- 3.5 Row 4 = 1 3 3 0 6 2 2 6 2 6 = 31 -- 5.5 Row 5 = 1 0 0 4 2 4 2 3 4 0 = 20 -- 1 Row 6 = 1 3 3 0 6 4 6 3 4 2 = 32 -- 7 Row 7 = 5 6 6 2 4 0 0 6 0 2 = 31 -- 5.5 Row 8 - 5 3 3 6 0 4 4 0 4 0 = 29 -- 3.5 What I require is a formula which will automatically insert the appropriate value, 1 to 8 in the example above. I would be most grateful for any suggestions. -- JohnD |
#5
|
|||
|
|||
Formula required.
Hi,
One way would be =RANK(K1,$K$1:$K$8,1)+(COUNT($K$1:$K$8)+1-RANK(K1,$K$1:$K$8,0)-RANK(K1,$K$1:$K$8,1))/2 Patrik On Fri, 23 Jan 2009 19:11:48 +0000, sherbrooke wrote: I have rows of figures over a number of columns, starting at column A, with totals for each row in column K. I then want to allocate values from 1 to 8 to each of the rows, in column L, with 8 to the highest total and 1 to the lowest total, if 2 rows are the same total they each receive half of the combined figures, as per the examples below:- (This is a very simple example of what I use, in reality there are some 24 columns and 16 rows, where the values are from 1 to 16 rather than 1 to 8) ABCDE FG H I J K L Row 1 = 5 6 6 2 4 2 4 3 2 6 = 40 -- 8 Row 2 = 5 3 3 6 0 2 0 3 2 4 = 28 -- 2 Row 3 = 1 0 0 4 2 6 6 0 6 4 = 29 -- 3.5 Row 4 = 1 3 3 0 6 2 2 6 2 6 = 31 -- 5.5 Row 5 = 1 0 0 4 2 4 2 3 4 0 = 20 -- 1 Row 6 = 1 3 3 0 6 4 6 3 4 2 = 32 -- 7 Row 7 = 5 6 6 2 4 0 0 6 0 2 = 31 -- 5.5 Row 8 - 5 3 3 6 0 4 4 0 4 0 = 29 -- 3.5 What I require is a formula which will automatically insert the appropriate value, 1 to 8 in the example above. I would be most grateful for any suggestions. |
#6
|
|||
|
|||
Formula required.
Actually, of the formulas submitted so far (mine included), none of them
account for 3 or more way ties. This formula does... =RANK(K1,K$1:K$8,1)+(COUNTIF(K$1:K$8,K1)1)/COUNTIF(K$1:K$8,K1) -- Rick (MVP - Excel) "sherbrooke" wrote in message ... I have rows of figures over a number of columns, starting at column A, with totals for each row in column K. I then want to allocate values from 1 to 8 to each of the rows, in column L, with 8 to the highest total and 1 to the lowest total, if 2 rows are the same total they each receive half of the combined figures, as per the examples below:- (This is a very simple example of what I use, in reality there are some 24 columns and 16 rows, where the values are from 1 to 16 rather than 1 to 8) ABCDE FG H I J K L Row 1 = 5 6 6 2 4 2 4 3 2 6 = 40 -- 8 Row 2 = 5 3 3 6 0 2 0 3 2 4 = 28 -- 2 Row 3 = 1 0 0 4 2 6 6 0 6 4 = 29 -- 3.5 Row 4 = 1 3 3 0 6 2 2 6 2 6 = 31 -- 5.5 Row 5 = 1 0 0 4 2 4 2 3 4 0 = 20 -- 1 Row 6 = 1 3 3 0 6 4 6 3 4 2 = 32 -- 7 Row 7 = 5 6 6 2 4 0 0 6 0 2 = 31 -- 5.5 Row 8 - 5 3 3 6 0 4 4 0 4 0 = 29 -- 3.5 What I require is a formula which will automatically insert the appropriate value, 1 to 8 in the example above. I would be most grateful for any suggestions. -- JohnD |
#7
|
|||
|
|||
Formula required.
Rick,
I'm aware the OP said ties get 1/2 the value indicating no 3 way ties but may this mod to make it more generic =RANK(L1,L$1:L$8,1)+ROUND((COUNTIF(L$1:L$8,L1)1)/COUNTIF(L$1:L$8,L1),2) Mike "Rick Rothstein" wrote: Actually, this formula is a little simpler... =RANK(K2,K$1:K$8,1)+(COUNTIF(K$1:K$8,K2)1)/2 -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Try this formula... =(2*RANK(K1,K$1:K$8,1)+(COUNTIF(K$1:K$8,K1)1))/2 changing the ranges to match your actual conditions, of course. -- Rick (MVP - Excel) "sherbrooke" wrote in message ... I have rows of figures over a number of columns, starting at column A, with totals for each row in column K. I then want to allocate values from 1 to 8 to each of the rows, in column L, with 8 to the highest total and 1 to the lowest total, if 2 rows are the same total they each receive half of the combined figures, as per the examples below:- (This is a very simple example of what I use, in reality there are some 24 columns and 16 rows, where the values are from 1 to 16 rather than 1 to 8) ABCDE FG H I J K L Row 1 = 5 6 6 2 4 2 4 3 2 6 = 40 -- 8 Row 2 = 5 3 3 6 0 2 0 3 2 4 = 28 -- 2 Row 3 = 1 0 0 4 2 6 6 0 6 4 = 29 -- 3.5 Row 4 = 1 3 3 0 6 2 2 6 2 6 = 31 -- 5.5 Row 5 = 1 0 0 4 2 4 2 3 4 0 = 20 -- 1 Row 6 = 1 3 3 0 6 4 6 3 4 2 = 32 -- 7 Row 7 = 5 6 6 2 4 0 0 6 0 2 = 31 -- 5.5 Row 8 - 5 3 3 6 0 4 4 0 4 0 = 29 -- 3.5 What I require is a formula which will automatically insert the appropriate value, 1 to 8 in the example above. I would be most grateful for any suggestions. -- JohnD |
#8
|
|||
|
|||
Formula required.
Apologies I didn't see you'd posted this when I responded to your other post,
still think it's better with round to get rid of reccuring decimals Mike "Rick Rothstein" wrote: Actually, of the formulas submitted so far (mine included), none of them account for 3 or more way ties. This formula does... =RANK(K1,K$1:K$8,1)+(COUNTIF(K$1:K$8,K1)1)/COUNTIF(K$1:K$8,K1) -- Rick (MVP - Excel) "sherbrooke" wrote in message ... I have rows of figures over a number of columns, starting at column A, with totals for each row in column K. I then want to allocate values from 1 to 8 to each of the rows, in column L, with 8 to the highest total and 1 to the lowest total, if 2 rows are the same total they each receive half of the combined figures, as per the examples below:- (This is a very simple example of what I use, in reality there are some 24 columns and 16 rows, where the values are from 1 to 16 rather than 1 to 8) ABCDE FG H I J K L Row 1 = 5 6 6 2 4 2 4 3 2 6 = 40 -- 8 Row 2 = 5 3 3 6 0 2 0 3 2 4 = 28 -- 2 Row 3 = 1 0 0 4 2 6 6 0 6 4 = 29 -- 3.5 Row 4 = 1 3 3 0 6 2 2 6 2 6 = 31 -- 5.5 Row 5 = 1 0 0 4 2 4 2 3 4 0 = 20 -- 1 Row 6 = 1 3 3 0 6 4 6 3 4 2 = 32 -- 7 Row 7 = 5 6 6 2 4 0 0 6 0 2 = 31 -- 5.5 Row 8 - 5 3 3 6 0 4 4 0 4 0 = 29 -- 3.5 What I require is a formula which will automatically insert the appropriate value, 1 to 8 in the example above. I would be most grateful for any suggestions. -- JohnD |
#9
|
|||
|
|||
Formula required.
assuming that your data start in row 1, try this formula in L1 and copy down =SUM(1*(L1=$L$1:$L$8))-(SUM(1*(L1=$L$1:$L$8))-1)/2 Does this do what you what? -- Your feedback is very much appreciate, pls click on the Yes button below if this posting is helpful. Thank You cheers, francis "sherbrooke" wrote: I have rows of figures over a number of columns, starting at column A, with totals for each row in column K. I then want to allocate values from 1 to 8 to each of the rows, in column L, with 8 to the highest total and 1 to the lowest total, if 2 rows are the same total they each receive half of the combined figures, as per the examples below:- (This is a very simple example of what I use, in reality there are some 24 columns and 16 rows, where the values are from 1 to 16 rather than 1 to 8) ABCDE FG H I J K L Row 1 = 5 6 6 2 4 2 4 3 2 6 = 40 -- 8 Row 2 = 5 3 3 6 0 2 0 3 2 4 = 28 -- 2 Row 3 = 1 0 0 4 2 6 6 0 6 4 = 29 -- 3.5 Row 4 = 1 3 3 0 6 2 2 6 2 6 = 31 -- 5.5 Row 5 = 1 0 0 4 2 4 2 3 4 0 = 20 -- 1 Row 6 = 1 3 3 0 6 4 6 3 4 2 = 32 -- 7 Row 7 = 5 6 6 2 4 0 0 6 0 2 = 31 -- 5.5 Row 8 - 5 3 3 6 0 4 4 0 4 0 = 29 -- 3.5 What I require is a formula which will automatically insert the appropriate value, 1 to 8 in the example above. I would be most grateful for any suggestions. -- JohnD |
#10
|
|||
|
|||
Formula required.
forgot to mention that this is an array formula, after placing the formula,
press Ctrl, Shift and Enter all at once to enter, you will see curly brackets wrap around the formula in the formula bar. HTH -- Your feedback is very much appreciate, pls click on the Yes button below if this posting is helpful. Thank You cheers, francis "xlmate" wrote: assuming that your data start in row 1, try this formula in L1 and copy down =SUM(1*(L1=$L$1:$L$8))-(SUM(1*(L1=$L$1:$L$8))-1)/2 Does this do what you what? -- Your feedback is very much appreciate, pls click on the Yes button below if this posting is helpful. Thank You cheers, francis "sherbrooke" wrote: I have rows of figures over a number of columns, starting at column A, with totals for each row in column K. I then want to allocate values from 1 to 8 to each of the rows, in column L, with 8 to the highest total and 1 to the lowest total, if 2 rows are the same total they each receive half of the combined figures, as per the examples below:- (This is a very simple example of what I use, in reality there are some 24 columns and 16 rows, where the values are from 1 to 16 rather than 1 to 8) ABCDE FG H I J K L Row 1 = 5 6 6 2 4 2 4 3 2 6 = 40 -- 8 Row 2 = 5 3 3 6 0 2 0 3 2 4 = 28 -- 2 Row 3 = 1 0 0 4 2 6 6 0 6 4 = 29 -- 3.5 Row 4 = 1 3 3 0 6 2 2 6 2 6 = 31 -- 5.5 Row 5 = 1 0 0 4 2 4 2 3 4 0 = 20 -- 1 Row 6 = 1 3 3 0 6 4 6 3 4 2 = 32 -- 7 Row 7 = 5 6 6 2 4 0 0 6 0 2 = 31 -- 5.5 Row 8 - 5 3 3 6 0 4 4 0 4 0 = 29 -- 3.5 What I require is a formula which will automatically insert the appropriate value, 1 to 8 in the example above. I would be most grateful for any suggestions. -- JohnD |
Thread Tools | |
Display Modes | |
|
|