| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Arrow keys moving opposite direction | Excel Discussion (Misc queries) | |||
| How can I paste in the opposite direction? | Excel Discussion (Misc queries) | |||
| My arrow keys in Excel move the opposite direction | Excel Discussion (Misc queries) | |||
| Opposite of Concatenation! | Excel Discussion (Misc queries) | |||
| Opposite of SUM function is ?? | Excel Discussion (Misc queries) | |||
Linear Mode
