ranking over a sum
 

is there a way to use the rank function over a sum of two cells (well,
columns) without creating a third column with the sum in it?

so, if i have column a and column b both filled with numbers, i want to
assign a rank based on a + b without doing something like rank(c1,c:c)
where c is a + b.


--
dreamz
------------------------------------------------------------------------
dreamz's Profile: http://www.excelforum.com/member.php...o&userid=26462
View this thread: http://www.excelforum.com/showthread...hreadid=535591

ranking over a sum
 

Assuming that A2:B10 contains the data, try...

C2, copied down:

=SUMPRODUCT(--(A2+B2<A$2:A$10+B$2:B$10))+1

Hope this helps!

In article ,
dreamz wrote:

is there a way to use the rank function over a sum of two cells (well,
columns) without creating a third column with the sum in it?

so, if i have column a and column b both filled with numbers, i want to
assign a rank based on a + b without doing something like rank(c1,c:c)
where c is a + b.

ranking over a sum
 

One way

=MATCH(A1+B1,LARGE($A$1:$A$10+$B$1:$B$10,ROW(INDIR ECT("1:"&ROWS($A$1:$A$10))
)),0)

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"dreamz" wrote in
message ...

is there a way to use the rank function over a sum of two cells (well,
columns) without creating a third column with the sum in it?

so, if i have column a and column b both filled with numbers, i want to
assign a rank based on a + b without doing something like rank(c1,c:c)
where c is a + b.


--
dreamz
------------------------------------------------------------------------
dreamz's Profile:
http://www.excelforum.com/member.php...o&userid=26462
View this thread: http://www.excelforum.com/showthread...hreadid=535591

ranking over a sum
 

thanks for the replies!

domenic, your formula works nicely.


--
dreamz
------------------------------------------------------------------------
dreamz's Profile: http://www.excelforum.com/member.php...o&userid=26462
View this thread: http://www.excelforum.com/showthread...hreadid=535591