# Opposite direction

B

#### 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

R

#### Rick Rothstein

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

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

T

#### T. Valko

Another one...

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

Copied down

R

#### 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))

R

#### Rick Rothstein

Cute... I like it!

--
Rick (MVP - Excel)

T. Valko said:
Another one...

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

Copied down

B

#### Branko Pecar

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

Branko

T

#### T. Valko

You're welcome!

--
Biff
Microsoft Excel MVP

Branko Pecar said:
Very, very cool. A real life saver. Thanks guys.

Branko

B

#### 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

B

#### 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

#### T. Valko

Good job!

--
Biff
Microsoft Excel MVP

Branko Pecar said:
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