Opposite direction

  • Thread starter Thread starter Branko Pecar
  • Start date Start date
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
 
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))
 
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
 
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
 
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
Click to expand...
 
Back
Top