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" <Mary@discussions.microsoft.com> wrote in message
news:587178F6-EB30-414C-855A-345C0DC29FED@microsoft.com...
>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" <Mary@discussions.microsoft.com> wrote in message
news:587178F6-EB30-414C-855A-345C0DC29FED@microsoft.com...
>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 there are multiple instances of rank1 that will not give the results that
it seems the OP wants.

Biff

"Krishnakumar" <Krishnakumar.1v33mg_1126256766.0858@excelforum-nospam.com>
wrote in message
news:Krishnakumar.1v33mg_1126256766.0858@excelforum-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
>