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

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

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

Top