Evaluating Rank to determine formula

[Guest]

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.

 

[Arvi Laanemets]

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)

 

[Biff]

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

 

[Krishnakumar]

Hi,

=IF(B1=1,A1-LARGE($A$1:$A$4,2),A1-LARGE($A$1:$A$4,1))

HTH

 

[Biff]

Hi!

If there are multiple instances of rank1 that will not give the results that
it seems the OP wants.

Biff

"Krishnakumar" <[email protected]>
wrote in message
news:[email protected]...

 

[Krishnakumar]

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