Opposite direction

[Branko Pecar]

Assume I have numbers in cells A1:A5 and B1:B5. I would like to add them in
column C, but following this formula: C1=A1+B5, C2=A2+B2,...,C5=A5+C1. How
do I do it so that I can just use copy paste of this formula without
manually re-entering it in every cell? Thanks.

Branko

 

[Rick Rothstein]

Try this formula in C1 and then copy it down...

=A1+INDEX(B1:B5,6-ROW(A1))

 

[T. Valko]

Another one...

=A1+INDEX(B$1:B$5,ROWS(B1:B$5))

Copied down

 

[Rick Rothstein]

I just noticed I forgot to make the range in the first argument absolute
(which is necessary so that you can correctly copy it down)...

=A1+INDEX(B$1:B$5,6-ROW(A1))

 

[Rick Rothstein]

Cute... I like it!

--
Rick (MVP - Excel)

Another one...

=A1+INDEX(B$1:B$5,ROWS(B1:B$5))

Copied down

 

[Branko Pecar]

Very, very cool. A real life saver. Thanks guys.

Branko

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

Very, very cool. A real life saver. Thanks guys.

Branko

 

[Branko Pecar]

OK. Here is a bit more complicated spin on the same problem. Assume I have
numbers in A1:A5. I would like in column B to put a formula:

B1=SUMPRODUCT($A$1:A5,A1:$A$5)
B2=SUMPRODUCT($A$1:A4,A2:$A$5)
..
..
B5=SUMPRODUCT($A$1:A1,A5:$A$5)

The challenge is to copy down the formula, but the first range is moving in
the opposite direction. Any ideas how to solve this? Many thanks.

Branko

 

[Branko Pecar]

Sorry for messing you about. I found a solution to my previous question:

B1=SUMPRODUCT($A$1:INDEX($A$1:$A$5,ROWS(A1:$A$5)),A1:$A$5)
B2=SUMPRODUCT($A$1:INDEX($A$1:$A$5,ROWS(A2:$A$5)),A2:$A$5), etc.
B5=SUMPRODUCT($A$1:INDEX($A$1:$A$5,ROWS(A5:$A$5)),A5:$A$5)

Thanks for leading me in the right direction.

Branko

 

[T. Valko]

Good job!

--
Biff
Microsoft Excel MVP

Sorry for messing you about. I found a solution to my previous question:

B1=SUMPRODUCT($A$1:INDEX($A$1:$A$5,ROWS(A1:$A$5)),A1:$A$5)
B2=SUMPRODUCT($A$1:INDEX($A$1:$A$5,ROWS(A2:$A$5)),A2:$A$5), etc.
B5=SUMPRODUCT($A$1:INDEX($A$1:$A$5,ROWS(A5:$A$5)),A5:$A$5)

Thanks for leading me in the right direction.

Branko