ranking over a sum

[dreamz]

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.

 

[Domenic]

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!

 

[Bob Phillips]

One way

=MATCH(A1+B1,LARGE($A$1:$A$10+$B$1:$B$10,ROW(INDIRECT("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]

thanks for the replies!

domenic, your formula works nicely.