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 |
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 |
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 |
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 |
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 |
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 |
All times are GMT +1. The time now is 09:20 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com