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

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

Similar Threads

Formula in opposite direction 1
Countif(and 1
Is there a formula that can do this? 3
Excel function - countif 2
using sumif & countif to sort multiple cells 3
Help with function 2
Copy Pasting formula's 1
SUMIF data that falls within a range 3

Back
Top