|
Opposite direction
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 |
Opposite direction
Try this formula in C1 and then copy it down...
=A1+INDEX(B1:B5,6-ROW(A1)) -- Rick (MVP - Excel) "Branko Pecar" wrote in message ... 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 |
Opposite direction
Another one...
=A1+INDEX(B$1:B$5,ROWS(B1:B$5)) Copied down -- Biff Microsoft Excel MVP "Branko Pecar" wrote in message ... 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 |
Opposite direction
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" wrote in message ... Try this formula in C1 and then copy it down... =A1+INDEX(B1:B5,6-ROW(A1)) -- Rick (MVP - Excel) "Branko Pecar" wrote in message ... 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 |
Opposite direction
Cute... I like it!
-- Rick (MVP - Excel) "T. Valko" wrote in message ... Another one... =A1+INDEX(B$1:B$5,ROWS(B1:B$5)) Copied down -- Biff Microsoft Excel MVP "Branko Pecar" wrote in message ... 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 |
Opposite direction
Very, very cool. A real life saver. Thanks guys.
Branko "Rick Rothstein" wrote in message ... Cute... I like it! -- Rick (MVP - Excel) "T. Valko" wrote in message ... Another one... =A1+INDEX(B$1:B$5,ROWS(B1:B$5)) Copied down -- Biff Microsoft Excel MVP "Branko Pecar" wrote in message ... 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 |
Opposite direction
You're welcome!
-- Biff Microsoft Excel MVP "Branko Pecar" wrote in message ... Very, very cool. A real life saver. Thanks guys. Branko "Rick Rothstein" wrote in message ... Cute... I like it! -- Rick (MVP - Excel) "T. Valko" wrote in message ... Another one... =A1+INDEX(B$1:B$5,ROWS(B1:B$5)) Copied down -- Biff Microsoft Excel MVP "Branko Pecar" wrote in message ... 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 |
Opposite direction
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" wrote in message ... You're welcome! -- Biff Microsoft Excel MVP "Branko Pecar" wrote in message ... Very, very cool. A real life saver. Thanks guys. Branko "Rick Rothstein" wrote in message ... Cute... I like it! -- Rick (MVP - Excel) "T. Valko" wrote in message ... Another one... =A1+INDEX(B$1:B$5,ROWS(B1:B$5)) Copied down -- Biff Microsoft Excel MVP "Branko Pecar" wrote in message ... 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 |
Opposite direction
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" wrote in message ... You're welcome! -- Biff Microsoft Excel MVP "Branko Pecar" wrote in message ... Very, very cool. A real life saver. Thanks guys. Branko "Rick Rothstein" wrote in message ... Cute... I like it! -- Rick (MVP - Excel) "T. Valko" wrote in message ... Another one... =A1+INDEX(B$1:B$5,ROWS(B1:B$5)) Copied down -- Biff Microsoft Excel MVP "Branko Pecar" wrote in message ... 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 |
Opposite direction
Good job!
-- Biff Microsoft Excel MVP "Branko Pecar" wrote in message ... 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" wrote in message ... You're welcome! -- Biff Microsoft Excel MVP "Branko Pecar" wrote in message ... Very, very cool. A real life saver. Thanks guys. Branko "Rick Rothstein" wrote in message ... Cute... I like it! -- Rick (MVP - Excel) "T. Valko" wrote in message ... Another one... =A1+INDEX(B$1:B$5,ROWS(B1:B$5)) Copied down -- Biff Microsoft Excel MVP "Branko Pecar" wrote in message ... 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 |
| All times are GMT +1. The time now is 08:37 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com