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? :confused:

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

Magic! That's a relief. Had visions of a worn out keyboard. :}

Many thanks, goldfrog! :ok:

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?

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

To get your sum of the past 100 results I'd suggest using a column that is the running total of all results and then a further column that is value n minus value (n-100).

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

@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

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

This feedback should help others who need this technique: the above quoted method works. ;)

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