need some help with an array function

[Lorin]

I want to replace a calculated column (Dif in example below), with an
array. I want to create the fourth column (Ranking) without having the
third column (Dif).

EXAMPLE:
A B Dif Ranking
12 11 1 1
34 22 12 4
16 12 4 2
18 13 5 3

Dif is A2-B2 for row 2, A3-B3 for row 3, etc.

My plan was to use =rank(A2-B2,A2:A5-B2:B5,0) and hit Ctrl+Shift+Enter
to tell it that A2:A5-B2:B5 is an array, but it isn't working. (Excel
won't let me enter that formula at all.) =rank(A2-B2,C2:C5,0) works as
expected. Obviously, I could calculate column C and hide it but this is
not what I want to do.

Can somebody help? This is a simpler problem then the one I recently
posted, but I think related (and makes me think the other is not
related to ISNA after all).

 

[Ron Rosenfeld]

I want to replace a calculated column (Dif in example below), with an
array. I want to create the fourth column (Ranking) without having the
third column (Dif).

EXAMPLE:
A B Dif Ranking
12 11 1 1
34 22 12 4
16 12 4 2
18 13 5 3

Dif is A2-B2 for row 2, A3-B3 for row 3, etc.

My plan was to use =rank(A2-B2,A2:A5-B2:B5,0) and hit Ctrl+Shift+Enter
to tell it that A2:A5-B2:B5 is an array, but it isn't working. (Excel
won't let me enter that formula at all.) =rank(A2-B2,C2:C5,0) works as
expected. Obviously, I could calculate column C and hide it but this is
not what I want to do.

Can somebody help? This is a simpler problem then the one I recently
posted, but I think related (and makes me think the other is not
related to ISNA after all).

You could try this array formula:

=MATCH(A2-B2,SMALL($A$2:$A$5-$B$2:$B$5,
ROW(INDIRECT("1:"&ROWS($A$2:$A$5)))),0)


--ron