| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need to rank values with ties
Could someone provide a duplicate ranks with decimal fractions algorithm. I
need to rank data that has tied values, but I want a ranking that is unique for each value, so I need to rank the data using decimals, (eg., 1, 2, 3.1, 3.2, 4, etc.). Thanks in advance |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need to rank values with ties
Chip Pearson explains it very well he
http://www.cpearson.com/excel/rank.aspx Hope this helps. Pete On Sep 22, 3:32*pm, MichaelZ wrote: Could someone provide a duplicate ranks with decimal fractions algorithm. *I need to rank data that has tied values, but I want a ranking that is unique for each value, so I need to rank the data using decimals, (eg., 1, 2, 3.1, 3.2, 4, etc.). Thanks in advance |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need to rank values with ties
Hi Michael z,
Suppose your data is in cell a2:a100 ,then do one thing sort above data in descending order. Put following formula in cell b2 and copy down. =COUNTIF($A$2:$A$100,A2) then put 1 in cell c2 and put following formula in cell c3 and copy down. =IF(AND(A2<A1,A2=A3),INT(C1)+1.1,IF(A2=A1,C1+0.1, INT(C1)+1)) You will get what you looking for. Harshawardhan . Shastri India "MichaelZ" wrote: Could someone provide a duplicate ranks with decimal fractions algorithm. I need to rank data that has tied values, but I want a ranking that is unique for each value, so I need to rank the data using decimals, (eg., 1, 2, 3.1, 3.2, 4, etc.). Thanks in advance |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need to rank values with ties
Hi,
With your data in a1 - A20 put this in b1 and drag down =SUMPRODUCT(($A$1:$A$20$A$1:$A$20 1:1)*(1/COUNTIF($A$1:$A$20,$A$1:A$20)))+(COUNTIF(INDEX($A$ 1:$A$20,1):INDEX($A$1:$A$20,ROW()-MIN(ROW($A$1:$A$20))+1),$A$1:$A$20)-1)/10+1.1 Mike "MichaelZ" wrote: Could someone provide a duplicate ranks with decimal fractions algorithm. I need to rank data that has tied values, but I want a ranking that is unique for each value, so I need to rank the data using decimals, (eg., 1, 2, 3.1, 3.2, 4, etc.). Thanks in advance |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need to rank values with ties
I can get you part way to what you asked for. You request is for 1, 2, 3.1,
3.2, 4... the 4 is the problem with my formula below... it will give the next item after the 3.1 and 3.2 the rank of 5 (because the 3.1 and 3.2 take the place of the 3/4 tie, so the next rank after them is 5, not 4). If that is OK, here is the formula... =RANK(A1,A$1:A$15,TRUE)&IF(SUMPRODUCT(--(RANK(A$1:A$11,A$1:A$15)=RANK(A1,A$1:A$15)))1,"." &SUMPRODUCT(--(RANK(A$1:A1,A$1:A$15)=RANK(A1,A$1:A$15))),"") -- Rick (MVP - Excel) "MichaelZ" wrote in message ... Could someone provide a duplicate ranks with decimal fractions algorithm. I need to rank data that has tied values, but I want a ranking that is unique for each value, so I need to rank the data using decimals, (eg., 1, 2, 3.1, 3.2, 4, etc.). Thanks in advance |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need to rank values with ties
Here is my previously posted formula in a little simpler format...
=RANK(A1,A$1:A$20)&IF(SUMPRODUCT(--(RANK(A$1:A$20,A:A)=RANK(A1,A:A)))1,"."&SUMPRODUC T(--(RANK(A$1:A1,A:A)=RANK(A1,A:A))),"") And, of course, change the A$20 reference to the last cell of data you have. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... I can get you part way to what you asked for. You request is for 1, 2, 3.1, 3.2, 4... the 4 is the problem with my formula below... it will give the next item after the 3.1 and 3.2 the rank of 5 (because the 3.1 and 3.2 take the place of the 3/4 tie, so the next rank after them is 5, not 4). If that is OK, here is the formula... =RANK(A1,A$1:A$15,TRUE)&IF(SUMPRODUCT(--(RANK(A$1:A$11,A$1:A$15)=RANK(A1,A$1:A$15)))1,"." &SUMPRODUCT(--(RANK(A$1:A1,A$1:A$15)=RANK(A1,A$1:A$15))),"") -- Rick (MVP - Excel) "MichaelZ" wrote in message ... Could someone provide a duplicate ranks with decimal fractions algorithm. I need to rank data that has tied values, but I want a ranking that is unique for each value, so I need to rank the data using decimals, (eg., 1, 2, 3.1, 3.2, 4, etc.). Thanks in advance |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need to rank values with ties
Rick,
I got them sequential but can't get rid of the .1 when there is only 1 instance of a number. I've changed mine to sort in the same order as yours and the comparative outputs are below. Can you get rid of the .1 for a single instance in my formula, I've got no idea how and it's driving me nuts?? There's a typo in the second rank statement of your formula. =SUMPRODUCT(($A$1:$A$15<$A$1:$A$15 1:1)*(1/COUNTIF($A$1:$A$15,$A$1:A$15)))+(COUNTIF(INDEX($A$ 1:$A$15,1):INDEX($A$1:$A$15,ROW()-MIN(ROW($A$1:$A$15))+1),$A$1:$A$15)-1)/10+1.1 Values Yours Mike 15 15 11.1 14 14 10.1 13 11.1 9.1 13 11.2 9.2 13 11.3 9.3 12 10 8.1 11 9 7.1 10 8 6.1 9 7 5.1 8 5.1 4.1 8 5.2 4.2 7 4 3.1 6 2 2.1 6 2 2.2 5 1 1.1 Mike "Rick Rothstein" wrote: I can get you part way to what you asked for. You request is for 1, 2, 3.1, 3.2, 4... the 4 is the problem with my formula below... it will give the next item after the 3.1 and 3.2 the rank of 5 (because the 3.1 and 3.2 take the place of the 3/4 tie, so the next rank after them is 5, not 4). If that is OK, here is the formula... =RANK(A1,A$1:A$15,TRUE)&IF(SUMPRODUCT(--(RANK(A$1:A$11,A$1:A$15)=RANK(A1,A$1:A$15)))1,"." &SUMPRODUCT(--(RANK(A$1:A1,A$1:A$15)=RANK(A1,A$1:A$15))),"") -- Rick (MVP - Excel) "MichaelZ" wrote in message ... Could someone provide a duplicate ranks with decimal fractions algorithm. I need to rank data that has tied values, but I want a ranking that is unique for each value, so I need to rank the data using decimals, (eg., 1, 2, 3.1, 3.2, 4, etc.). Thanks in advance |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need to rank values with ties
How about this?
=SUMPRODUCT(($A$1:$A$20<$A$1:$A$20 1:1)*(1/COUNTIF($A$1:$A$20,$A$1:A$20)))+(COUNTIF(INDEX($A$ 1:$A$20,1):INDEX($A$1:$A$20,ROW()-MIN(ROW($A$1:$A$20))+1),$A$1:$A$20)-1)/10+1+0.1*(SUMPRODUCT(--(RANK(A$1:A$20,A:A)=RANK(A1,A:A)))1) -- Rick (MVP - Excel) "Mike H" wrote in message ... Rick, I got them sequential but can't get rid of the .1 when there is only 1 instance of a number. I've changed mine to sort in the same order as yours and the comparative outputs are below. Can you get rid of the .1 for a single instance in my formula, I've got no idea how and it's driving me nuts?? There's a typo in the second rank statement of your formula. =SUMPRODUCT(($A$1:$A$15<$A$1:$A$15 1:1)*(1/COUNTIF($A$1:$A$15,$A$1:A$15)))+(COUNTIF(INDEX($A$ 1:$A$15,1):INDEX($A$1:$A$15,ROW()-MIN(ROW($A$1:$A$15))+1),$A$1:$A$15)-1)/10+1.1 Values Yours Mike 15 15 11.1 14 14 10.1 13 11.1 9.1 13 11.2 9.2 13 11.3 9.3 12 10 8.1 11 9 7.1 10 8 6.1 9 7 5.1 8 5.1 4.1 8 5.2 4.2 7 4 3.1 6 2 2.1 6 2 2.2 5 1 1.1 Mike "Rick Rothstein" wrote: I can get you part way to what you asked for. You request is for 1, 2, 3.1, 3.2, 4... the 4 is the problem with my formula below... it will give the next item after the 3.1 and 3.2 the rank of 5 (because the 3.1 and 3.2 take the place of the 3/4 tie, so the next rank after them is 5, not 4). If that is OK, here is the formula... =RANK(A1,A$1:A$15,TRUE)&IF(SUMPRODUCT(--(RANK(A$1:A$11,A$1:A$15)=RANK(A1,A$1:A$15)))1,"." &SUMPRODUCT(--(RANK(A$1:A1,A$1:A$15)=RANK(A1,A$1:A$15))),"") -- Rick (MVP - Excel) "MichaelZ" wrote in message ... Could someone provide a duplicate ranks with decimal fractions algorithm. I need to rank data that has tied values, but I want a ranking that is unique for each value, so I need to rank the data using decimals, (eg., 1, 2, 3.1, 3.2, 4, etc.). Thanks in advance |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need to rank values with ties
Perhaps,
=IF(COUNTIF($A$1:$A$15,A1)=1,SUMPRODUCT(($A$1:$A$1 5<$A$1:$A$15 1:1)*(1/COUNTIF($A$1:$A$15,$A$1:A$15)))+1,SUMPRODUCT(($A$1 :$A$15<$A$1:$A$15 1:1)*(1/COUNTIF($A$1:$A$15,$A$1:A$15)))+(COUNTIF(INDEX($A$ 1:$A$15,1):INDEX($A$1:$A$15,ROW()-MIN(ROW($A$1:$A$15))+1),$A$1:$A$15)-1)/10+1.1) Mike "Mike H" wrote: Rick, I got them sequential but can't get rid of the .1 when there is only 1 instance of a number. I've changed mine to sort in the same order as yours and the comparative outputs are below. Can you get rid of the .1 for a single instance in my formula, I've got no idea how and it's driving me nuts?? There's a typo in the second rank statement of your formula. =SUMPRODUCT(($A$1:$A$15<$A$1:$A$15 1:1)*(1/COUNTIF($A$1:$A$15,$A$1:A$15)))+(COUNTIF(INDEX($A$ 1:$A$15,1):INDEX($A$1:$A$15,ROW()-MIN(ROW($A$1:$A$15))+1),$A$1:$A$15)-1)/10+1.1 Values Yours Mike 15 15 11.1 14 14 10.1 13 11.1 9.1 13 11.2 9.2 13 11.3 9.3 12 10 8.1 11 9 7.1 10 8 6.1 9 7 5.1 8 5.1 4.1 8 5.2 4.2 7 4 3.1 6 2 2.1 6 2 2.2 5 1 1.1 Mike "Rick Rothstein" wrote: I can get you part way to what you asked for. You request is for 1, 2, 3.1, 3.2, 4... the 4 is the problem with my formula below... it will give the next item after the 3.1 and 3.2 the rank of 5 (because the 3.1 and 3.2 take the place of the 3/4 tie, so the next rank after them is 5, not 4). If that is OK, here is the formula... =RANK(A1,A$1:A$15,TRUE)&IF(SUMPRODUCT(--(RANK(A$1:A$11,A$1:A$15)=RANK(A1,A$1:A$15)))1,"." &SUMPRODUCT(--(RANK(A$1:A1,A$1:A$15)=RANK(A1,A$1:A$15))),"") -- Rick (MVP - Excel) "MichaelZ" wrote in message ... Could someone provide a duplicate ranks with decimal fractions algorithm. I need to rank data that has tied values, but I want a ranking that is unique for each value, so I need to rank the data using decimals, (eg., 1, 2, 3.1, 3.2, 4, etc.). Thanks in advance |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need to rank values with ties
Rick,
Nice one, I cracked it a different way in my other post:) Mike "Rick Rothstein" wrote: How about this? =SUMPRODUCT(($A$1:$A$20<$A$1:$A$20 1:1)*(1/COUNTIF($A$1:$A$20,$A$1:A$20)))+(COUNTIF(INDEX($A$ 1:$A$20,1):INDEX($A$1:$A$20,ROW()-MIN(ROW($A$1:$A$20))+1),$A$1:$A$20)-1)/10+1+0.1*(SUMPRODUCT(--(RANK(A$1:A$20,A:A)=RANK(A1,A:A)))1) -- Rick (MVP - Excel) "Mike H" wrote in message ... Rick, I got them sequential but can't get rid of the .1 when there is only 1 instance of a number. I've changed mine to sort in the same order as yours and the comparative outputs are below. Can you get rid of the .1 for a single instance in my formula, I've got no idea how and it's driving me nuts?? There's a typo in the second rank statement of your formula. =SUMPRODUCT(($A$1:$A$15<$A$1:$A$15 1:1)*(1/COUNTIF($A$1:$A$15,$A$1:A$15)))+(COUNTIF(INDEX($A$ 1:$A$15,1):INDEX($A$1:$A$15,ROW()-MIN(ROW($A$1:$A$15))+1),$A$1:$A$15)-1)/10+1.1 Values Yours Mike 15 15 11.1 14 14 10.1 13 11.1 9.1 13 11.2 9.2 13 11.3 9.3 12 10 8.1 11 9 7.1 10 8 6.1 9 7 5.1 8 5.1 4.1 8 5.2 4.2 7 4 3.1 6 2 2.1 6 2 2.2 5 1 1.1 Mike "Rick Rothstein" wrote: I can get you part way to what you asked for. You request is for 1, 2, 3.1, 3.2, 4... the 4 is the problem with my formula below... it will give the next item after the 3.1 and 3.2 the rank of 5 (because the 3.1 and 3.2 take the place of the 3/4 tie, so the next rank after them is 5, not 4). If that is OK, here is the formula... =RANK(A1,A$1:A$15,TRUE)&IF(SUMPRODUCT(--(RANK(A$1:A$11,A$1:A$15)=RANK(A1,A$1:A$15)))1,"." &SUMPRODUCT(--(RANK(A$1:A1,A$1:A$15)=RANK(A1,A$1:A$15))),"") -- Rick (MVP - Excel) "MichaelZ" wrote in message ... Could someone provide a duplicate ranks with decimal fractions algorithm. I need to rank data that has tied values, but I want a ranking that is unique for each value, so I need to rank the data using decimals, (eg., 1, 2, 3.1, 3.2, 4, etc.). Thanks in advance |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to break ties in Rank by specified criteria? | Excel Discussion (Misc queries) | |||
| Rank without skipping for ties | Excel Worksheet Functions | |||
| RANK with 0 values | Excel Worksheet Functions | |||
| Using Rank with ties and spliting the differance | Excel Discussion (Misc queries) | |||
| How to use RANK to break multiple ties. | Excel Worksheet Functions | |||
Linear Mode
