Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Evaluating Rank to determine formula
I have three columns:
A B C 98 1 3 83 4 -15 95 2 -3 90 3 -8 Column "A" contains the numbers I am trying to evaluate. In column "B", I have used the Rank function on Column "A". In column "C", I want the corresponding column "A" number subtracted from the #1 ranked number in column "A". I will want to copy this formula all the way across the chart to evaluate other numbers. I have figured out how to do this with the Rank function, but now I am stumped and don't know how to apporach this. Is there a function that will do this? Do I need to use an If-then function? This needs to be something I can do myslef since I will be continually adding on to this chart with a new range of numbers I will want to evaluate. Any help is greatly appreciated. -- Mary |
#2
|
|||
|
|||
Hi
At start, you explanation doesn't match with your example figures at all! Why is there 3 matching to 98, instead of 0? For all other rows, you have /from corresponding column "A" number subtracted the #1 ranked number in column "A"/ really, i.e. 83-98=-15; 95-98=-3, etc. To calculate this, you don't need to know ranking at all. C1=A1-MAX(A:A) "Mary" wrote in message ... I have three columns: A B C 98 1 3 83 4 -15 95 2 -3 90 3 -8 Column "A" contains the numbers I am trying to evaluate. In column "B", I have used the Rank function on Column "A". In column "C", I want the corresponding column "A" number subtracted from the #1 ranked number in column "A". I will want to copy this formula all the way across the chart to evaluate other numbers. I have figured out how to do this with the Rank function, but now I am stumped and don't know how to apporach this. Is there a function that will do this? Do I need to use an If-then function? This needs to be something I can do myslef since I will be continually adding on to this chart with a new range of numbers I will want to evaluate. Any help is greatly appreciated. -- Mary |
#3
|
|||
|
|||
Hi!
This does what you want but it seems overly complicated to me and maybe there's a better way: Entere as an array using the key combo of CTRL,SHIFT,ENTER: =IF(A1=MAX(A$1:A$20),A1-INDEX(A$1:A$20,MATCH(TRUE,B$1:B$20=MIN(IF(B$1:B$20 MIN(B$1:B$20),B$1:B$20)),0)),A1-MAX(A$1:A$20)) Copy down column C as needed. Biff "Mary" wrote in message ... I have three columns: A B C 98 1 3 83 4 -15 95 2 -3 90 3 -8 Column "A" contains the numbers I am trying to evaluate. In column "B", I have used the Rank function on Column "A". In column "C", I want the corresponding column "A" number subtracted from the #1 ranked number in column "A". I will want to copy this formula all the way across the chart to evaluate other numbers. I have figured out how to do this with the Rank function, but now I am stumped and don't know how to apporach this. Is there a function that will do this? Do I need to use an If-then function? This needs to be something I can do myslef since I will be continually adding on to this chart with a new range of numbers I will want to evaluate. Any help is greatly appreciated. -- Mary |
#4
|
|||
|
|||
Hi, =IF(B1=1,A1-LARGE($A$1:$A$4,2),A1-LARGE($A$1:$A$4,1)) HTH -- Krishnakumar ------------------------------------------------------------------------ Krishnakumar's Profile: http://www.excelforum.com/member.php...o&userid=20138 View this thread: http://www.excelforum.com/showthread...hreadid=466162 |
#5
|
|||
|
|||
Hi!
If there are multiple instances of rank1 that will not give the results that it seems the OP wants. Biff "Krishnakumar" wrote in message news:Krishnakumar.1v33mg_1126256766.0858@excelforu m-nospam.com... Hi, =IF(B1=1,A1-LARGE($A$1:$A$4,2),A1-LARGE($A$1:$A$4,1)) HTH -- Krishnakumar ------------------------------------------------------------------------ Krishnakumar's Profile: http://www.excelforum.com/member.php...o&userid=20138 View this thread: http://www.excelforum.com/showthread...hreadid=466162 |
#6
|
|||
|
|||
Hi, If there are multiple instances of rank1 that will not give the results that it seems the OP wants. One way to overcome this to change the rank formula. In B1 and copied down, =RANK(A1,$A$1:$A$4)+COUNTIF($A$1:A1,A1)-1 HTH -- Krishnakumar ------------------------------------------------------------------------ Krishnakumar's Profile: http://www.excelforum.com/member.php...o&userid=20138 View this thread: http://www.excelforum.com/showthread...hreadid=466162 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to determine row of hi value filtered column | Excel Discussion (Misc queries) | |||
Excel Formula to determine if cells contain certain data | Excel Worksheet Functions | |||
Formula to determine shift | Excel Worksheet Functions | |||
Cell doesn't show formula result - it shows formula (CTRL + ' doe. | Excel Worksheet Functions | |||
formula to determine the first column containing any data | Excel Worksheet Functions |