Opposite direction

Branko Pecar

unread,

Nov 22, 2009, 9:14:34 PM11/22/09

to

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

unread,

Nov 22, 2009, 9:22:42 PM11/22/09

to

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

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

--
Rick (MVP - Excel)

"Branko Pecar" <bpe...@sbcglobal.net> wrote in message
news:eXYI0K%23aKH...@TK2MSFTNGP05.phx.gbl...

T. Valko

unread,

Nov 22, 2009, 10:21:36 PM11/22/09

to

Another one...

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

Copied down

--
Biff
Microsoft Excel MVP

"Branko Pecar" <bpe...@sbcglobal.net> wrote in message
news:eXYI0K%23aKH...@TK2MSFTNGP05.phx.gbl...

Rick Rothstein

unread,

Nov 22, 2009, 11:19:08 PM11/22/09

to

Cute... I like it!

--
Rick (MVP - Excel)

"T. Valko" <biffi...@comcast.net> wrote in message
news:%23VZnQw%23aKH...@TK2MSFTNGP06.phx.gbl...

Rick Rothstein

unread,

Nov 22, 2009, 11:18:37 PM11/22/09

to

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 (MVP - Excel)

"Rick Rothstein" <rick.new...@NO.SPAMverizon.net> wrote in message
news:%23$MM4P%23aKH...@TK2MSFTNGP04.phx.gbl...

Branko Pecar

unread,

Nov 24, 2009, 10:30:10 PM11/24/09

to

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

Branko

"Rick Rothstein" <rick.new...@NO.SPAMverizon.net> wrote in message

news:uOqE8Q$aKHA...@TK2MSFTNGP05.phx.gbl...

T. Valko

unread,

Nov 24, 2009, 11:03:52 PM11/24/09

to

You're welcome!

--
Biff
Microsoft Excel MVP

"Branko Pecar" <bpe...@sbcglobal.net> wrote in message

news:ebG5Z%23XbK...@TK2MSFTNGP05.phx.gbl...

Branko Pecar

unread,

Nov 24, 2009, 11:54:39 PM11/24/09

to

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

"T. Valko" <biffi...@comcast.net> wrote in message

news:%23KJwNRY...@TK2MSFTNGP04.phx.gbl...

Branko Pecar

unread,

Nov 25, 2009, 12:18:29 AM11/25/09

to

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" <biffi...@comcast.net> wrote in message

news:%23KJwNRY...@TK2MSFTNGP04.phx.gbl...

T. Valko

unread,

Nov 25, 2009, 12:47:31 AM11/25/09

to

Good job!

--
Biff
Microsoft Excel MVP

"Branko Pecar" <bpe...@sbcglobal.net> wrote in message

news:uqH396Yb...@TK2MSFTNGP06.phx.gbl...