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

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 a
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.

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

Try this...

Entered in C1 and copied down to C4:

=SUMPRODUCT(--(A1+B1A$1:A$4+B$1:B$4))+1

--
Biff
Microsoft Excel MVP


"CalculatedArray" wrote in
message ...
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 a
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.

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

A beautiful formula!
--
Gary''s Student - gsnu200829


"T. Valko" wrote:

Try this...

Entered in C1 and copied down to C4:

=SUMPRODUCT(--(A1+B1A$1:A$4+B$1:B$4))+1

--
Biff
Microsoft Excel MVP


"CalculatedArray" wrote in
message ...
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 a
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.

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

Thanks!

--
Biff
Microsoft Excel MVP


"Gary''s Student" wrote in message
...
A beautiful formula!
--
Gary''s Student - gsnu200829


"T. Valko" wrote:

Try this...

Entered in C1 and copied down to C4:

=SUMPRODUCT(--(A1+B1A$1:A$4+B$1:B$4))+1

--
Biff
Microsoft Excel MVP


"CalculatedArray" wrote in
message ...
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 a
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.