Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Breaking a tie - Excel 2003
Okay.....I have a ranked column of numbers from which there might be a tie.
In order to break the tie I have three tie-breaking conditions (tie-breaker #1, tie-breaker #2, and tie-breaker #3). The rank is from lowest to highest (smaller is better) BUT the tie-breaking conditions are from highest to lowest (a larger number is better). I'd like to find a way, without using VB, to create a "tie breaking rank" that applies the conditions of the three tie-breakers in order to rank the original range of values; the tied ones and the unique ones. Below is a sample set of values: Total Rank TB1 TB2 TB3 My Wish 170 10 0 2 0 10 122 7 3 2 1 7 96 6 1 0 1 6 72 3 3 2 2 3 14 1 7 10 1 1 78 4 3 3 4 4 69 2 4 1 4 2 78 4 1 1 4 5 128 9 1 1 1 9 122 7 3 0 3 8 Also, I do not want to base the new ranking on the original ranking as I have some formatting and such within those cells that I do not want to change. So the new ranking needs to be based on the "total" and the three tie breaking conditions. I included the "my wish" column for what I hope the new formula does for me..... Hope someone can help! Robert. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Breaking a tie - Excel 2003
You could make a new total by adding those tie-breakers to the current
total. As they operate highest to lowest, you should subtract them from the maximum within the range, i.e.: =A2 + (MAX(C$2:C$11)-C2) + (MAX(D$2:D$11)-D2) + (MAX(E$2:E$11)-E2) Copy this down a helper column, then apply your rank formula to this. If your tie-breakers have different weights, then you can multiply them by some weighting factor, eg: =1000*A2 + 100*(MAX(C$2:C$11)-C2) + 10*(MAX(D$2:D$11)-D2) + (MAX(E$2:E $11)-E2) Hope this helps. Pete On Oct 14, 1:22*am, Robert wrote: Okay.....I have a ranked column of numbers from which there might be a tie. * In order to break the tie I have three tie-breaking conditions (tie-breaker #1, tie-breaker #2, and tie-breaker #3). *The rank is from lowest to highest (smaller is better) BUT the tie-breaking conditions are from highest to lowest (a larger number is better). *I'd like to find a way, without using VB, to create a "tie breaking rank" that applies the conditions of the three tie-breakers in order to rank the original range of values; the tied ones and the unique ones. * Below is a sample set of values: Total * Rank * *TB1 * * TB2 * * TB3 * * My Wish 170 * * 10 * * *0 * * * 2 * * * 0 * * * 10 122 * * 7 * * * 3 * * * 2 * * * 1 * * * 7 96 * * *6 * * * 1 * * * 0 * * * 1 * * * 6 72 * * *3 * * * 3 * * * 2 * * * 2 * * * 3 14 * * *1 * * * 7 * * * 10 * * *1 * * * 1 78 * * *4 * * * 3 * * * 3 * * * 4 * * * 4 69 * * *2 * * * 4 * * * 1 * * * 4 * * * 2 78 * * *4 * * * 1 * * * 1 * * * 4 * * * 5 128 * * 9 * * * 1 * * * 1 * * * 1 * * * 9 122 * * 7 * * * 3 * * * 0 * * * 3 * * * 8 Also, I do not want to base the new ranking on the original ranking as I have some formatting and such within those cells that I do not want to change. *So the new ranking needs to be based on the "total" and the three tie breaking conditions. *I included the "my wish" column for what I hope the new formula does for me..... Hope someone can help! Robert. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Breaking a tie - Excel 2003
Well.....each of the tie-breakers is weighted the same...no problem there.
However, I want to use tie-breaker #1 ONLY to break the initial tie. Then, if there is still a tie then I wish to employ tie-breaker #2...similar for the use of #3. The formula suggested would, mathematically, make it possible for two of the values to still be the same; as happened when utilized for the current data shown. The individual tie-breakers shown across each row should, ideally, be used only for the reference total....otherwise there might be some cross contamination of the final results. Certainly I can do this procedure manually but I'd really love to have a built-in function to do the same; show my new rank vs. my original rank. Really, I wish to break the tie for the tied numbers only....the others can simply be moved into the new ranking column. The final visual order is not as important as the correctness of the numerical rank. Keep up the good suggestions! Robert. "Pete_UK" wrote: You could make a new total by adding those tie-breakers to the current total. As they operate highest to lowest, you should subtract them from the maximum within the range, i.e.: =A2 + (MAX(C$2:C$11)-C2) + (MAX(D$2:D$11)-D2) + (MAX(E$2:E$11)-E2) Copy this down a helper column, then apply your rank formula to this. If your tie-breakers have different weights, then you can multiply them by some weighting factor, eg: =1000*A2 + 100*(MAX(C$2:C$11)-C2) + 10*(MAX(D$2:D$11)-D2) + (MAX(E$2:E $11)-E2) Hope this helps. Pete On Oct 14, 1:22 am, Robert wrote: Okay.....I have a ranked column of numbers from which there might be a tie. In order to break the tie I have three tie-breaking conditions (tie-breaker #1, tie-breaker #2, and tie-breaker #3). The rank is from lowest to highest (smaller is better) BUT the tie-breaking conditions are from highest to lowest (a larger number is better). I'd like to find a way, without using VB, to create a "tie breaking rank" that applies the conditions of the three tie-breakers in order to rank the original range of values; the tied ones and the unique ones. Below is a sample set of values: Total Rank TB1 TB2 TB3 My Wish 170 10 0 2 0 10 122 7 3 2 1 7 96 6 1 0 1 6 72 3 3 2 2 3 14 1 7 10 1 1 78 4 3 3 4 4 69 2 4 1 4 2 78 4 1 1 4 5 128 9 1 1 1 9 122 7 3 0 3 8 Also, I do not want to base the new ranking on the original ranking as I have some formatting and such within those cells that I do not want to change. So the new ranking needs to be based on the "total" and the three tie breaking conditions. I included the "my wish" column for what I hope the new formula does for me..... Hope someone can help! Robert. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Breaking a tie - Excel 2003
Well, this worked for me. With your data in A1:F11, put this in G2:
=IF(COUNTIF(A$2:A$11,A2)1,(1000*A2+100*(MAX(C$2:C $11)-C2)+10*(MAX(D $2:D$11)-D2)+(MAX(E$2:E$11)-E2))/1000,A2) and this in H2: =RANK(G2,G$2:G$11,1) It gave the same ranks as your wish column. Hope this helps. Pete On Oct 14, 2:36*am, Robert wrote: Well.....each of the tie-breakers is weighted the same...no problem there.. * However, I want to use tie-breaker #1 ONLY to break the initial tie. *Then, if there is still a tie then I wish to employ tie-breaker #2...similar for the use of #3. *The formula suggested would, mathematically, make it possible for two of the values to still be the same; as happened when utilized for the current data shown. *The individual tie-breakers shown across each row should, ideally, be used only for the reference total....otherwise there might be some cross contamination of the final results. *Certainly I can do this procedure manually but I'd really love to have a built-in function to do the same; show my new rank vs. my original rank. * Really, I wish to break the tie for the tied numbers only....the others can simply be moved into the new ranking column. *The final visual order is not as important as the correctness of the numerical rank. Keep up the good suggestions! Robert. "Pete_UK" wrote: You could make a new total by adding those tie-breakers to the current total. As they operate highest to lowest, you should subtract them from the maximum within the range, i.e.: =A2 + (MAX(C$2:C$11)-C2) + (MAX(D$2:D$11)-D2) + (MAX(E$2:E$11)-E2) Copy this down a helper column, then apply your rank formula to this. If your tie-breakers have different weights, then you can multiply them by some weighting factor, eg: =1000*A2 + 100*(MAX(C$2:C$11)-C2) + 10*(MAX(D$2:D$11)-D2) + (MAX(E$2:E $11)-E2) Hope this helps. Pete On Oct 14, 1:22 am, Robert wrote: Okay.....I have a ranked column of numbers from which there might be a tie. * In order to break the tie I have three tie-breaking conditions (tie-breaker #1, tie-breaker #2, and tie-breaker #3). *The rank is from lowest to highest (smaller is better) BUT the tie-breaking conditions are from highest to lowest (a larger number is better). *I'd like to find a way, without using VB, to create a "tie breaking rank" that applies the conditions of the three tie-breakers in order to rank the original range of values; the tied ones and the unique ones. * Below is a sample set of values: Total * Rank * *TB1 * * TB2 * * TB3 * * My Wish 170 * * 10 * * *0 * * * 2 * * * 0 * * * 10 122 * * 7 * * * 3 * * * 2 * * * 1 * * * 7 96 * * *6 * * * 1 * * * 0 * * * 1 * * * 6 72 * * *3 * * * 3 * * * 2 * * * 2 * * * 3 14 * * *1 * * * 7 * * * 10 * * *1 * * * 1 78 * * *4 * * * 3 * * * 3 * * * 4 * * * 4 69 * * *2 * * * 4 * * * 1 * * * 4 * * * 2 78 * * *4 * * * 1 * * * 1 * * * 4 * * * 5 128 * * 9 * * * 1 * * * 1 * * * 1 * * * 9 122 * * 7 * * * 3 * * * 0 * * * 3 * * * 8 Also, I do not want to base the new ranking on the original ranking as I have some formatting and such within those cells that I do not want to change. *So the new ranking needs to be based on the "total" and the three tie breaking conditions. *I included the "my wish" column for what I hope the new formula does for me..... Hope someone can help! Robert.- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Breaking a tie - Excel 2003
Pete,
Looks like this might work. I originally used an unweighted count...so the weighted version seems to do what I need. I have 30 total rows of data to rank this way so I do appreciate the assistance. robert. "Pete_UK" wrote: Well, this worked for me. With your data in A1:F11, put this in G2: =IF(COUNTIF(A$2:A$11,A2)1,(1000*A2+100*(MAX(C$2:C $11)-C2)+10*(MAX(D $2:D$11)-D2)+(MAX(E$2:E$11)-E2))/1000,A2) and this in H2: =RANK(G2,G$2:G$11,1) It gave the same ranks as your wish column. Hope this helps. Pete On Oct 14, 2:36 am, Robert wrote: Well.....each of the tie-breakers is weighted the same...no problem there.. However, I want to use tie-breaker #1 ONLY to break the initial tie. Then, if there is still a tie then I wish to employ tie-breaker #2...similar for the use of #3. The formula suggested would, mathematically, make it possible for two of the values to still be the same; as happened when utilized for the current data shown. The individual tie-breakers shown across each row should, ideally, be used only for the reference total....otherwise there might be some cross contamination of the final results. Certainly I can do this procedure manually but I'd really love to have a built-in function to do the same; show my new rank vs. my original rank. Really, I wish to break the tie for the tied numbers only....the others can simply be moved into the new ranking column. The final visual order is not as important as the correctness of the numerical rank. Keep up the good suggestions! Robert. "Pete_UK" wrote: You could make a new total by adding those tie-breakers to the current total. As they operate highest to lowest, you should subtract them from the maximum within the range, i.e.: =A2 + (MAX(C$2:C$11)-C2) + (MAX(D$2:D$11)-D2) + (MAX(E$2:E$11)-E2) Copy this down a helper column, then apply your rank formula to this. If your tie-breakers have different weights, then you can multiply them by some weighting factor, eg: =1000*A2 + 100*(MAX(C$2:C$11)-C2) + 10*(MAX(D$2:D$11)-D2) + (MAX(E$2:E $11)-E2) Hope this helps. Pete On Oct 14, 1:22 am, Robert wrote: Okay.....I have a ranked column of numbers from which there might be a tie. In order to break the tie I have three tie-breaking conditions (tie-breaker #1, tie-breaker #2, and tie-breaker #3). The rank is from lowest to highest (smaller is better) BUT the tie-breaking conditions are from highest to lowest (a larger number is better). I'd like to find a way, without using VB, to create a "tie breaking rank" that applies the conditions of the three tie-breakers in order to rank the original range of values; the tied ones and the unique ones. Below is a sample set of values: Total Rank TB1 TB2 TB3 My Wish 170 10 0 2 0 10 122 7 3 2 1 7 96 6 1 0 1 6 72 3 3 2 2 3 14 1 7 10 1 1 78 4 3 3 4 4 69 2 4 1 4 2 78 4 1 1 4 5 128 9 1 1 1 9 122 7 3 0 3 8 Also, I do not want to base the new ranking on the original ranking as I have some formatting and such within those cells that I do not want to change. So the new ranking needs to be based on the "total" and the three tie breaking conditions. I included the "my wish" column for what I hope the new formula does for me..... Hope someone can help! Robert.- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Breaking a tie - Excel 2003
You're welcome, Robert - thanks for feeding back.
Pete On Oct 14, 1:39*pm, Robert wrote: Pete, Looks like this might work. *I originally used an unweighted count...so the weighted version seems to do what I need. *I have 30 total rows of data to rank this way so I do appreciate the assistance. robert. "Pete_UK" wrote: Well, this worked for me. With your data in A1:F11, put this in G2: =IF(COUNTIF(A$2:A$11,A2)1,(1000*A2+100*(MAX(C$2:C $11)-C2)+10*(MAX(D $2:D$11)-D2)+(MAX(E$2:E$11)-E2))/1000,A2) and this in H2: =RANK(G2,G$2:G$11,1) It gave the same ranks as your wish column. Hope this helps. Pete On Oct 14, 2:36 am, Robert wrote: Well.....each of the tie-breakers is weighted the same...no problem there.. * However, I want to use tie-breaker #1 ONLY to break the initial tie. *Then, if there is still a tie then I wish to employ tie-breaker #2...similar for the use of #3. *The formula suggested would, mathematically, make it possible for two of the values to still be the same; as happened when utilized for the current data shown. *The individual tie-breakers shown across each row should, ideally, be used only for the reference total....otherwise there might be some cross contamination of the final results. *Certainly I can do this procedure manually but I'd really love to have a built-in function to do the same; show my new rank vs. my original rank. * Really, I wish to break the tie for the tied numbers only....the others can simply be moved into the new ranking column. *The final visual order is not as important as the correctness of the numerical rank. Keep up the good suggestions! Robert. "Pete_UK" wrote: You could make a new total by adding those tie-breakers to the current total. As they operate highest to lowest, you should subtract them from the maximum within the range, i.e.: =A2 + (MAX(C$2:C$11)-C2) + (MAX(D$2:D$11)-D2) + (MAX(E$2:E$11)-E2) Copy this down a helper column, then apply your rank formula to this. If your tie-breakers have different weights, then you can multiply them by some weighting factor, eg: =1000*A2 + 100*(MAX(C$2:C$11)-C2) + 10*(MAX(D$2:D$11)-D2) + (MAX(E$2:E $11)-E2) Hope this helps. Pete On Oct 14, 1:22 am, Robert wrote: Okay.....I have a ranked column of numbers from which there might be a tie. * In order to break the tie I have three tie-breaking conditions (tie-breaker #1, tie-breaker #2, and tie-breaker #3). *The rank is from lowest to highest (smaller is better) BUT the tie-breaking conditions are from highest to lowest (a larger number is better). *I'd like to find a way, without using VB, to create a "tie breaking rank" that applies the conditions of the three tie-breakers in order to rank the original range of values; the tied ones and the unique ones. * Below is a sample set of values: Total * Rank * *TB1 * * TB2 * * TB3 * * My Wish 170 * * 10 * * *0 * * * 2 * * * 0 * * * 10 122 * * 7 * * * 3 * * * 2 * * * 1 * * * 7 96 * * *6 * * * 1 * * * 0 * * * 1 * * * 6 72 * * *3 * * * 3 * * * 2 * * * 2 * * * 3 14 * * *1 * * * 7 * * * 10 * * *1 * * * 1 78 * * *4 * * * 3 * * * 3 * * * 4 * * * 4 69 * * *2 * * * 4 * * * 1 * * * 4 * * * 2 78 * * *4 * * * 1 * * * 1 * * * 4 * * * 5 128 * * 9 * * * 1 * * * 1 * * * 1 * * * 9 122 * * 7 * * * 3 * * * 0 * * * 3 * * * 8 Also, I do not want to base the new ranking on the original ranking as I have some formatting and such within those cells that I do not want to change. *So the new ranking needs to be based on the "total" and the three tie breaking conditions. *I included the "my wish" column for what I hope the new formula does for me..... Hope someone can help! Robert.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Non Breaking Hyphen in 2003 | Excel Discussion (Misc queries) | |||
Breaking down an excel formula | Excel Worksheet Functions | |||
Breaking Links in Excel 2000 | Excel Discussion (Misc queries) | |||
command for breaking link in excel is not seen. | Excel Discussion (Misc queries) | |||
How do I prevent my links from breaking in Excel? | Excel Worksheet Functions |