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
|