Can I use a 'calculated' array in a function?


C

CalculatedArray

for example, I have 2 columns of data:
3 6
4 2
1 1
7 3

I want to RANK the sum of each row but do not want to store the sum in
another column first.

the sums are:
9
6
2
10

so in column C, I want to see
3
2
1
4

Resulting sheet would look like:
3 6 3
4 2 2
1 1 1
7 3 4

I tried this for the formula in C and it did not like it ($ present to make
it easy for fill down):
=RANK(a1+b1,($a$1+$b$1):($a$4+$b$4),1)

I tried SHIFT+CTRL+ENTER thinking it might be an array formula but that did
not work either.

Again, the crux of my dilemma is that I do not want to store the sum in
another column prior to ranking.
 
Ad

Advertisements

T

T. Valko

Try this...

Entered in C1 and copied down to C4:

=SUMPRODUCT(--(A1+B1>A$1:A$4+B$1:B$4))+1
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top