# Microsoft Office Application Help - Excel Help forum > Excel Programming / VBA / Macros >  >  Is it possible to create a recursive formula?

## lamkoid

Hello all,

This is my first post on this forum, so I apologize for any breaches in protocol, and I will endeavor to correct them.

I have a large column of data in C1 from R1 to R5000. 

I would like to use the SUM function to analyze the data.

In C2 I would like the SUM of the current row in C1 and the previous row in C1.

I can do this in any row with the formula: =SUM(R[-1]C1:RC1)

In C3 I would like the SUM of the current row in C1 and the previous two rows in C1.

I can do this in any row with the formula: =SUM(R[-2]C1:RC1)

In each column to the right I would like the SUM function to increase by one increment.

Ideally, I would have a generic SUM function that I could drop into any cell in C2, then using the Fill Handel, copy the function to the right so that in column n the function read: =SUM(R[-n]C1:RC1).

Is this possible?

----------


## JBeaucaire

Manually inserted in cell C1 would be the formula:

*=SUM($A$1:$A1)*

Then just double-click the lower right corner of that cell to automatically copy that cell down the entire data range matching the length of data in column A.  All done.


==========
The VBA way of doing the same thing would be:




```
Please Login or Register  to view this content.
```

----------


## lamkoid

Hey JBeaucaire,

Thanks for the quick reply. And that right corner double click trick is very nifty and will come in handy.

But the formula you provided gives me the running sum of all the previous data going down the chart. 

I.e. if I run that formula down column B, in B10 I get a SUM of the data from A1 to A10, and in B11 I get a SUM of the data from A1 to A11.

What I need in each cell of column B is just the SUM of the data in the corresponding row in column A with the data in the preceding row of column A.

I.e. in B10 I need the SUM of A10 and A9, and in B11 I need the Sum of A11 and A10.

I can achieve this in column B by using this variation of the formula you provided: =SUM($A1:$A2) and just starting in row 2. As this formula is copied down the column, it gives me a running 2 SUM.

I can then modify the formula to calculate the running 3 SUM function in column C by inserting: =SUM($A1:$A3) in C3 and copying the formula down the column with the double click trick you suggestedd.

The problem is that I have to modify the formula for each column as I move to the right, and doing that a couple of thousand times would require to get my medication levels upwardly revised- if you get my drift.

Ideally, I need a formula that when copied to the right increases the running sum by one increment for each column it moves to the right. 

If I needed to calculate all the running sums from 2 up to 1000, I could start in cell B1000, copy the formula across to the right 1000 columns using the fill handle, and then fill the columns up and down.

----------


## JBeaucaire

Then the formula will have to start in row 2 at C2 simply as:
*=A1 + A2*

Copy that down.

In VBA:



```
Please Login or Register  to view this content.
```

----------


## JBeaucaire

Ah...the lights just came on.  OK, this will require an INDIRECT() formula, and INDIRECT() is a volatile function, so if you put 1000s of these on your sheet, it may slow down perceptibly.

In B2 put this formula (not C2)...
*=SUM(INDIRECT("r" & ROW() & "c1:r" & ROW()+COLUMN()-2 & "c1",0))*

Copy that down and to the right as far as you want.

That will add the row you're on plus the number of rows down equal to the number of columns to the right you move.

If you have never used the FORMULA AUDITING toolbar, this would be a good time to try it out.

NOTE: if you wanted to start in column C, you would need to increase the part in *red* I highlighted.

----------


## lamkoid

Hey JBeaucaire,

Ah yes, The 'ol INDIRECT() formula. Actually I have no idea what that is, but from once majoring in math, and doing some programing in Fortran some 30 years ago, I knew that running this type of spreadsheet was going to be a hairball for my computer.

The good news is that the formula you provided:

=SUM(INDIRECT("r" & ROW() & "c1:r" & ROW()+COLUMN()-2 & "c1",0))

came very close to solving the problem. 

I think the only problem is that you designed it to:

"... add the row you're on plus the number of rows down equal to the number of columns to the right you move."

But what I need is to add the row I'm on plus the number of rows *UP* equal to the number of columns to the right I move.

I don't know much about programming, but I do know I wouldn't touch that formula with a fork.

----------


## JBeaucaire

That's easy enough to reverse:
*=SUM(INDIRECT("r" & ROW() & "c1:r" & ROW()-COLUMN()+2 & "c1",0))*

But that will give you errors if you drag it off to the right in row1. Since you're using Excel 2007, you can trap those errors like so:

*=IFERROR(SUM(INDIRECT("r" & ROW() & "c1:r" & ROW()-COLUMN()+2 & "c1",0)), "")*


NOTE: If you've never used the FORMULA AUDITING toolbar, Evaluate Formula icon, this would be a good formula to try it on.

----------


## lecxe

Hi

Another option.

A bit bigger but is non volatile and traps the possible error in any version of excel. In cell C2:

=IF(ROWS($A$1:$A2)<COLUMNS($C2:C2)+1,"",SUM($A2:INDEX($A:$A,ROWS($A$1:$A2)-COLUMNS($C2:C2))))

Copy down and accross

HTH

----------


## lamkoid

Hey JBeaucaire,

That did the trick nicely.

As you suggested it is, as they say, computationally intensive. I pulled it out to a 3000 x 3000 array and a little mushroom cloud started forming over my computer.

Which makes me wonder if there is a more elegant solution- and I fully realize there may not be.

If the columns are indexed by numbers instead of letters, and I use the original formula I posted: 

=SUM(R[-1]C1:RC1)

This produces the running 2 sum for column 2- assuming the data is in column 1.

Similarly:

=SUM(R[-2]C1:RC1)

Produces the running 3 sum for column 3.

So that for column _n_, the running *n* sum would look like:

=SUM(R[-(*n*-1)]C1:RC1)

This is all contingent on the possibility that the column index can be referenced in the formula.

Is that possible?

----------


## JBeaucaire

> ...non volatile and traps the possible error in any version of excel. In cell C2:
> 
> =IF(ROWS($A$1:$A2)<COLUMNS($C2:C2)+1,"",SUM($A2:INDEX($A:$A,ROWS($A$1:$A2)-COLUMNS($C2:C2))))
> 
> Copy down and accross



That took me a minute to absorb, and it's a great technique. The formula needs just "little" tweaking to get the right values, though.

*=IF(ROWS($A$1:$A2)<COLUMNS($B$2:B2)+1,"",SUM($A2:INDEX($A:$A,ROWS($A$1:$A2)-COLUMNS($B$2:B2)+1)))*

----------


## lecxe

> The formula needs just "little" tweaking to get the right values, though.



JBeaucaire, thank you for correcting the formula. I wrote it to start at cell C2 but I see now that the OP wanted to start at cell B2.

----------


## lamkoid

Hey JBeaucaire & lecxe,

I checked the formula and it works great. Thank you so much for taking the time to help me out. 

JBeaucaire, that was a very valuable lesson in INDIRECT() formulas and volatility. And thanks again for taking the time to tweak the formula to get it right.

lecxe, I appreciate the time you took to find a non volatile formula, and that conditional prefix was something I had not seen before. 

Great lesson guys!

----------


## Darkened

This thread is 10 years old yet I still feel the need to improve on the formula.

=sum(indirect("N" & if(row()-(column()-columns($A$1:$N$1))>0,row()-(column()-columns($A$1:$N$1)),1)):indirect("N"& row()))

This will let you do exactly what you want. N is the column that your data set is in, so you will want to change it to whatever column you use. 

Originally, the formula would require you to have an outside reference:





> Hey JBeaucaire,
> 
> That did the trick nicely.
> 
> As you suggested it is, as they say, computationally intensive. I pulled it out to a 3000 x 3000 array and a little mushroom cloud started forming over my computer.
> 
> Which makes me wonder if there is a more elegant solution- and I fully realize there may not be.
> 
> If the columns are indexed by numbers instead of letters, and I use the original formula I posted: 
> ...



Like mentioned in the end here.

Luckily, I was able to arrange it to the point where that is not entirely necessary. I simply calculated the difference between the column with data (N in this case) and the current column ( column() ). I then created a range from the current row to the row minus difference in columns. In order to prevent this from going past any spreadsheet barriers (N0,N"-1", other negative rows), I added the IF statement to just reset any negative row numbers to 1.

No clue if anyone will read this, but I was searching for recursive formulas when I ended up being able to figure it out from the information provided here.

Anyways,

Happy spreadsheeting!  :Smilie:

----------


## AliGW

It may well help - the thread has had over 48000 views since it was started. Thanks for posting.  :Smilie:

----------


## nott0m

I have to say, I'm not sure why I'm responding to a 6 month old replay to an 11 year old post but I have to wonder why everyone used INDIRECT instead of OFFSET.



```
Please Login or Register  to view this content.
```

----------

