Excel - Applying formulae down a column and calculating a rolling sum
Thread Starter
Join Date: Aug 2002
Location: England.
Posts: 440
Likes: 0
Received 0 Likes
on
0 Posts
Excel - Applying formulae down a column and calculating a rolling sum
Excel question, please, if any experts are watching in.
I've searched some of the online help forums and am getting nowhere.
To simplify the description:
Assume, to begin with:
I have a column of four numbers in cells A1 to A4
I have a column of four numbers in cells B1 to B4
In column C1 I need cell A1 contents multiplied by 2 minus cell B1 contents
In column C2 I need cell A2 contents multiplied by 2 minus cell B2 contents
In column C3 I need cell A3 contents multiplied by 2 minus cell B3 contents
In column C4 I need cell A4 contents multiplied by 2 minus cell B4 contents
I could insert:
=2*A1-B1 in cell C1
=2*A2-B2 in cell C2
=2*A3-B3 in cell C3
=2*A4-B4 in cell C4
Unfortunately, I currently have 450 numbers in each A and B column, not just four.
How could I quickly (without typing the required equation 450 times, with appropriate alteration each time) achieve the above operation 450 times, please?
Having achieved that, I then need to calculate a rolling sum of 100 adjacent 'C column' numbers, starting at C1 and:
Displaying the sum of C1 to C100 in cell D1
Displaying the sum of C2 to C101 in cell D2
Displaying the sum of C3 to C102 in cell D3
etc
etc
Can anyone advise, please?
I've searched some of the online help forums and am getting nowhere.
To simplify the description:
Assume, to begin with:
I have a column of four numbers in cells A1 to A4
I have a column of four numbers in cells B1 to B4
In column C1 I need cell A1 contents multiplied by 2 minus cell B1 contents
In column C2 I need cell A2 contents multiplied by 2 minus cell B2 contents
In column C3 I need cell A3 contents multiplied by 2 minus cell B3 contents
In column C4 I need cell A4 contents multiplied by 2 minus cell B4 contents
I could insert:
=2*A1-B1 in cell C1
=2*A2-B2 in cell C2
=2*A3-B3 in cell C3
=2*A4-B4 in cell C4
Unfortunately, I currently have 450 numbers in each A and B column, not just four.
How could I quickly (without typing the required equation 450 times, with appropriate alteration each time) achieve the above operation 450 times, please?
Having achieved that, I then need to calculate a rolling sum of 100 adjacent 'C column' numbers, starting at C1 and:
Displaying the sum of C1 to C100 in cell D1
Displaying the sum of C2 to C101 in cell D2
Displaying the sum of C3 to C102 in cell D3
etc
etc
Can anyone advise, please?
Join Date: Jan 2003
Location: Retired to Leafy Bucks
Posts: 94
Likes: 0
Received 0 Likes
on
0 Posts
In C1 enter =(A1*2)-B1
Then press enter
Then select C1 again and double click on the little box in the bottom right of the cell
As if by magic the formula will populate all the cells below with data in A and B
Then enter =SUM(C1:C100) in to cell D1
Then press enter
Then select D1 again and drag the little box in the bottom right of the cell until you have enough cells populated with the desired result
Then press enter
Then select C1 again and double click on the little box in the bottom right of the cell
As if by magic the formula will populate all the cells below with data in A and B
Then enter =SUM(C1:C100) in to cell D1
Then press enter
Then select D1 again and drag the little box in the bottom right of the cell until you have enough cells populated with the desired result
Thread Starter
Join Date: Aug 2002
Location: England.
Posts: 440
Likes: 0
Received 0 Likes
on
0 Posts
OK, correction to my last.....not quite so magic, I regret.
I've only just got around to tackling this and have discovered that the suggested method merely populates all cells with the same formula. I need a method that will populate each cell with a formula that adapts to the cell it is in.
I've typed out the formulae by hand for the first three cells (ignore the cell numbers in my first post - this is a fresh example).
Cell a1.....=c38-d37
Cell a2.....=c39-d37
Cell a3.....=c40-d37
I now need to continue that sequence almost four hundred times for cell a4, a5, a6 etc., altering the formulae to c41-d37, c42-d37, c43-d37 etc
Are there any 'quick' ways of continuing those sequences, without typing them all out by hand, please?
I've only just got around to tackling this and have discovered that the suggested method merely populates all cells with the same formula. I need a method that will populate each cell with a formula that adapts to the cell it is in.
I've typed out the formulae by hand for the first three cells (ignore the cell numbers in my first post - this is a fresh example).
Cell a1.....=c38-d37
Cell a2.....=c39-d37
Cell a3.....=c40-d37
I now need to continue that sequence almost four hundred times for cell a4, a5, a6 etc., altering the formulae to c41-d37, c42-d37, c43-d37 etc
Are there any 'quick' ways of continuing those sequences, without typing them all out by hand, please?
Join Date: Feb 2008
Location: Planet Zog
Posts: 5
Likes: 0
Received 0 Likes
on
0 Posts
Hello
In cell C1 add =sum(a1*2)-b1 when you have a value in C1 put your cursor in that cell and press copy, then hi-light the cells you need ie C2 onward, then press paste. This will insert the correct formulea into all the cells hi-lighted. Hope this is OK
Geoff
In cell C1 add =sum(a1*2)-b1 when you have a value in C1 put your cursor in that cell and press copy, then hi-light the cells you need ie C2 onward, then press paste. This will insert the correct formulea into all the cells hi-lighted. Hope this is OK
Geoff
Join Date: Aug 2002
Location: Earth
Posts: 3,663
Likes: 0
Received 0 Likes
on
0 Posts
I know it's not necessarily answering the original question, but whilst helping someone else today, I discoverd the sumproduct function which I'd not discovered before.
So just incase it helps anyone in the future, this site has quite a good overview of the capabilities of sumproduct :
Help using the Excel SUMPRODUCT() function
acbus1,
Re. your second question, perhaps an indirect reference might be along the lines of what you desire ?
Help using the Excel INDIRECT function
So just incase it helps anyone in the future, this site has quite a good overview of the capabilities of sumproduct :
Help using the Excel SUMPRODUCT() function
acbus1,
Re. your second question, perhaps an indirect reference might be along the lines of what you desire ?
Help using the Excel INDIRECT function
Join Date: Mar 2003
Location: England
Posts: 286
Likes: 0
Received 0 Likes
on
0 Posts
@abcus1:
You need need to use an absolute reference to achieve this.
Normal excel references are 'relative' - ie:
If you have =A1+B1 in cell c1 and copy it down to c2 a relative reference will change relative to the copy eg =A2+B2.
Absolute references do not change at all. You tell excel to use absolute references by including a dollar sign $. You can 'fix' both the row and the column or just a row or a column. You can also have both absolute and relative references in a formula.
So using our example of a copy from c1 to c2:
= $A$1+$B$1 changes to = $A$1+$B$1
= A1+$B$1 changes to =A2+$B$1
= $A1+$B$1 changes to = $A2+$B$1
if you copied from c1 to d2 then:
= $A$1+$B$1 changes to = $A$1+$B$1
= A1+$B$1 changes to =B2+$B$1
= $A1+$B$1 changes to = $A2+$B$1
So in your example you need to enter in a1 : =c38-$d$37
Then copy it to the other cells.
EG
Cell a1.....=c38-d37
Cell a2.....=c39-d37
Cell a3.....=c40-d37
I now need to continue that sequence almost four hundred times for cell a4, a5, a6 etc., altering the formulae to c41-d37, c42-d37, c43-d37 etc
Cell a2.....=c39-d37
Cell a3.....=c40-d37
I now need to continue that sequence almost four hundred times for cell a4, a5, a6 etc., altering the formulae to c41-d37, c42-d37, c43-d37 etc
Normal excel references are 'relative' - ie:
If you have =A1+B1 in cell c1 and copy it down to c2 a relative reference will change relative to the copy eg =A2+B2.
Absolute references do not change at all. You tell excel to use absolute references by including a dollar sign $. You can 'fix' both the row and the column or just a row or a column. You can also have both absolute and relative references in a formula.
So using our example of a copy from c1 to c2:
= $A$1+$B$1 changes to = $A$1+$B$1
= A1+$B$1 changes to =A2+$B$1
= $A1+$B$1 changes to = $A2+$B$1
if you copied from c1 to d2 then:
= $A$1+$B$1 changes to = $A$1+$B$1
= A1+$B$1 changes to =B2+$B$1
= $A1+$B$1 changes to = $A2+$B$1
So in your example you need to enter in a1 : =c38-$d$37
Then copy it to the other cells.
EG
Thread Starter
Join Date: Aug 2002
Location: England.
Posts: 440
Likes: 0
Received 0 Likes
on
0 Posts
Thanks guys n gals, I've now cracked it using the method:
captainspeaking2U
I need to check out ExGrunt's method, because that might aviod extra columns containing a common constant, which I'm creating by calculating the constant in the first cell of the column, then using captainspeaking2U's method to duplicate the calculation (albeit giving a constant result).
Main thing for now is to crunch the numbers (now grown to 2100 rows and ever-increasing) to meet a publishing deadline, so many thanks for all your help.
This feedback should help others who need this technique: the above quoted method works.
captainspeaking2U
In cell C1 add =sum(a1*2)-b1 when you have a value in C1 put your cursor in that cell and press copy, then hi-light the cells you need ie C2 onward, then press paste. This will insert the correct formulea into all the cells hi-lighted.
Main thing for now is to crunch the numbers (now grown to 2100 rows and ever-increasing) to meet a publishing deadline, so many thanks for all your help.
This feedback should help others who need this technique: the above quoted method works.
Join Date: Feb 2007
Location: Dublin, Ireland. (No, I just live here.)
Posts: 733
Likes: 0
Received 5 Likes
on
4 Posts
I agree with ExGrunt: learn to use reference locking with the $ sign. It's pretty much essential knowledge for getting the most from any spreadsheet package.