| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Separating equal values
Using the large function I can generate a list of high to low values. example:
In column E1 - E4 =LARGE(B1:B4,1) =LARGE(B1:B4,2) =LARGE(B1:B4,3) =LARGE(B1:B4,4) Next I have the names next to each value: In column D1- D4 =INDEX(A1:A4,MATCH(LARGE(B1:B4,1),B1:B4,FALSE),1) =INDEX(A1:A4,MATCH(LARGE(B1:B4,2),B1:B4,FALSE),1) =INDEX(A1:A4,MATCH(LARGE(B1:B4,3),B1:B4,FALSE),1) =INDEX(A1:A4,MATCH(LARGE(B1:B4,4),B1:B4,FALSE),1) So if my chart was: A B Alpha 10 Bravo 2 Charlie 6 Delta 8 The result would be: D E Alpha 10 Delta 8 Charlie 6 Bravo 2 Here's my problem. Lets go back to the original chart with new values. A B Alpha 10 Bravo 2 Charlie 8 Delta 8 The result would now be: D E Alpha 10 Charlie 8 Charlie 8 Bravo 2 Which omits Deltas score. Is there a way I can tell the program to, if theres a duplicate value, ignore the previous result? Obviously this is a much simpler version than the 1 I have at present which contains in excess of 70 names and results. |
|
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
|
Separating equal values
Faced with a similar problem, this was my workaround - may not be clever but
it worked for me. Assuming the table begins in A1, in C1 enter =RANK(B1,$B$1:$B$4,1)+COUNTIF($B$1:B1,B1)/100 Carefully note the mix of absolute and relative references in the COUNTIF Copy down the column to give: 4.01, 1.01, 2.01, 2.02 These are what I call 'modified rankings' Now use your LARGE as before but working on the C helper column Note that Delta will precede Charlie. To get the same order as the table use negation before the COUNTIF =RANK(B1,$B$1:$B$4,1) - COUNTIF($B$1:B1,B1)/100 best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Darren" wrote in message ... Using the large function I can generate a list of high to low values. example: In column E1 - E4 =LARGE(B1:B4,1) =LARGE(B1:B4,2) =LARGE(B1:B4,3) =LARGE(B1:B4,4) Next I have the names next to each value: In column D1- D4 =INDEX(A1:A4,MATCH(LARGE(B1:B4,1),B1:B4,FALSE),1) =INDEX(A1:A4,MATCH(LARGE(B1:B4,2),B1:B4,FALSE),1) =INDEX(A1:A4,MATCH(LARGE(B1:B4,3),B1:B4,FALSE),1) =INDEX(A1:A4,MATCH(LARGE(B1:B4,4),B1:B4,FALSE),1) So if my chart was: A B Alpha 10 Bravo 2 Charlie 6 Delta 8 The result would be: D E Alpha 10 Delta 8 Charlie 6 Bravo 2 Here's my problem. Lets go back to the original chart with new values. A B Alpha 10 Bravo 2 Charlie 8 Delta 8 The result would now be: D E Alpha 10 Charlie 8 Charlie 8 Bravo 2 Which omits Deltas score. Is there a way I can tell the program to, if theres a duplicate value, ignore the previous result? Obviously this is a much simpler version than the 1 I have at present which contains in excess of 70 names and results. |
|
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
|
Separating equal values
Thankyou so much Bernard. As for the comment of 'may not be clever', I don't
care how clumsy it looks it does the job. "Bernard Liengme" wrote: Faced with a similar problem, this was my workaround - may not be clever but it worked for me. Assuming the table begins in A1, in C1 enter =RANK(B1,$B$1:$B$4,1)+COUNTIF($B$1:B1,B1)/100 Carefully note the mix of absolute and relative references in the COUNTIF Copy down the column to give: 4.01, 1.01, 2.01, 2.02 These are what I call 'modified rankings' Now use your LARGE as before but working on the C helper column Note that Delta will precede Charlie. To get the same order as the table use negation before the COUNTIF =RANK(B1,$B$1:$B$4,1) - COUNTIF($B$1:B1,B1)/100 best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Darren" wrote in message ... Using the large function I can generate a list of high to low values. example: In column E1 - E4 =LARGE(B1:B4,1) =LARGE(B1:B4,2) =LARGE(B1:B4,3) =LARGE(B1:B4,4) Next I have the names next to each value: In column D1- D4 =INDEX(A1:A4,MATCH(LARGE(B1:B4,1),B1:B4,FALSE),1) =INDEX(A1:A4,MATCH(LARGE(B1:B4,2),B1:B4,FALSE),1) =INDEX(A1:A4,MATCH(LARGE(B1:B4,3),B1:B4,FALSE),1) =INDEX(A1:A4,MATCH(LARGE(B1:B4,4),B1:B4,FALSE),1) So if my chart was: A B Alpha 10 Bravo 2 Charlie 6 Delta 8 The result would be: D E Alpha 10 Delta 8 Charlie 6 Bravo 2 Here's my problem. Lets go back to the original chart with new values. A B Alpha 10 Bravo 2 Charlie 8 Delta 8 The result would now be: D E Alpha 10 Charlie 8 Charlie 8 Bravo 2 Which omits Deltas score. Is there a way I can tell the program to, if theres a duplicate value, ignore the previous result? Obviously this is a much simpler version than the 1 I have at present which contains in excess of 70 names and results. . |
|
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
|
Separating equal values
Thanks for the feedback
Bernard "Darren" wrote in message ... Thankyou so much Bernard. As for the comment of 'may not be clever', I don't care how clumsy it looks it does the job. "Bernard Liengme" wrote: Faced with a similar problem, this was my workaround - may not be clever but it worked for me. Assuming the table begins in A1, in C1 enter =RANK(B1,$B$1:$B$4,1)+COUNTIF($B$1:B1,B1)/100 Carefully note the mix of absolute and relative references in the COUNTIF Copy down the column to give: 4.01, 1.01, 2.01, 2.02 These are what I call 'modified rankings' Now use your LARGE as before but working on the C helper column Note that Delta will precede Charlie. To get the same order as the table use negation before the COUNTIF =RANK(B1,$B$1:$B$4,1) - COUNTIF($B$1:B1,B1)/100 best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Darren" wrote in message ... Using the large function I can generate a list of high to low values. example: In column E1 - E4 =LARGE(B1:B4,1) =LARGE(B1:B4,2) =LARGE(B1:B4,3) =LARGE(B1:B4,4) Next I have the names next to each value: In column D1- D4 =INDEX(A1:A4,MATCH(LARGE(B1:B4,1),B1:B4,FALSE),1) =INDEX(A1:A4,MATCH(LARGE(B1:B4,2),B1:B4,FALSE),1) =INDEX(A1:A4,MATCH(LARGE(B1:B4,3),B1:B4,FALSE),1) =INDEX(A1:A4,MATCH(LARGE(B1:B4,4),B1:B4,FALSE),1) So if my chart was: A B Alpha 10 Bravo 2 Charlie 6 Delta 8 The result would be: D E Alpha 10 Delta 8 Charlie 6 Bravo 2 Here's my problem. Lets go back to the original chart with new values. A B Alpha 10 Bravo 2 Charlie 8 Delta 8 The result would now be: D E Alpha 10 Charlie 8 Charlie 8 Bravo 2 Which omits Deltas score. Is there a way I can tell the program to, if theres a duplicate value, ignore the previous result? Obviously this is a much simpler version than the 1 I have at present which contains in excess of 70 names and results. . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Ranking Equal Values | Excel Worksheet Functions | |||
| Add different values that equal 1 on a row | Excel Worksheet Functions | |||
| counting the last 3 values not equal to x in a list | Excel Worksheet Functions | |||
| Dispay two values in one cell with the / border separating them. | Excel Worksheet Functions | |||
| How to add equal values and than replace them with their sum? | Excel Worksheet Functions | |||
Linear Mode
