Creating a Recursive Formula in a single cell?

Question

I have searched Google and stack exchange extensively and cannot find a solution that fits my needs.

I have a recursive formula in a Google Spreadsheet that can be represented as

F(0) = 0

F(1) = 5000

F(n) = F(n-1) + ((n-1) * 5000)

I can effectively calculate this with a long two column table with n values in column A and the recursive formula in column B which references the previous cell when needing to access F(n-1).

This approach is clunky, takes up space, and requires a separate table for every different calculation. In my sheet I need to calculate this formula for different values of F(0) and it requires a separate table for each one.

What I want is a simple single cell with a formula that references another cell for the n value and calculates the result recursively all in the background, instead of taking up a whole set of space in a table somewhere in my sheet for it to do the recursion through multiple cells.

My spreadsheet to make it clearer:

https://docs.google.com/spreadsheets/d/16kWw9iCvxExseNLdHQBWVGWybvDJgl8EHI9oDibbDSw/edit?usp=drivesdk

Is this possible?

Answer 1

If you go to your spreadsheet I solved your problem (at least I think so) in the 'progress' sheet and wrote up an explanation for how it works. Here's a quick summary, since I realize that answering a stack exchange question with only a broken link is terribly frustrating to future readers.

I treated F(1) and F(0) as constants that simply need to be added on to the end of the equation. Their only purpose is to show me the initial difference (in this case 5000). For reference in formulas, I created custom named ranges (Data>Named ranges) and called the cell with the n value (happened to be G2) the range n. The cell with F(0) is called fzero, and F(1) is called fone.

1. I wrote a formula to calculate the difference between any two output. e.g. F(n) - F(n-1) or in spreadsheet form (remember n is a named range pointing to G2):

=(n-1)*5000

2. Now, I realized that to calculate any F(n) value, I really only needed to add up the cell-to-cell differences of all the previous F(n) values. e.g. the sum of the set {F(0), F(1), F(2), F(3), ..., F(n-1)}. I accomplished this using the =sequence() command. Here, I'm telling sequence to start at 0 and count up by increments of 1 in a column 1 wide until it has printed n numbers.

=sequence(n,1,0)

3. Next, I multiplied the sequence by 5000 and surrounded it in arrayformula so that each individual term would be multiplied by 5000, rather than just the first one.

=arrayformula(sequence(n,1,0)*5000)

4. From here, it's a simple matter of adding all the differences together and tacking on the values of F(0) and F(1).

=fzero+fone+sum(arrayformula(sequence(n,1,0)*5000))

I didn't really do any testing on slightly modified equations or different starting values of F, but I think it would probably work with this general structure and slight modifications. If it isn't working, you could try altering the value of n by replacing it with n-1 or n+1 or even further like 2. I found a few times in development that my formula was close but it was off by one value of n and that modifying it in this way worked.

Answer 2

You want a formula to solve the recursive formula F(n) = F(n-1) + ((n-1) * 5000)

There may be many ways to solve your question; consider this as one solution.

This answer uses a "custom formula" wa14322603().

• Enter the formula in a cell and point the input value to a cell containing an integer. The formula will return the value of the recursive formula.

---

/**
 * Applies the input value to the recursive formula
 *
 * @param {number} input The value to use in the recursion
 * @return The input applies to the recursion.
 * F(0) = 0
 * F(1) = 5000
 * F(n) = F(n-1) + ((n-1) * 5000)
 * @customfunction
 */
function wa14322603(input) {

  // create a temporary array to hold values
  var resultarray=[];
  
  // assign F(0)
  resultarray.push(0);
  
  // assign F(1)
  resultarray.push(5000)
  
  // define the multiplier
  var multiplier = 5000;
  
  if (input===0){
    var result = resultarray[input]
  }
  
  if (input === 1){
    var result = resultarray[input]
  }
  
  if (input>1){

    // calculate the number of tertaions required
    var iterate=input+1;

    for (var i=2; i<iterate;i++){
    
      // calculate f(n-1}
      var fn_1=resultarray.slice(-1);

      // calculate n-1 * multiplier
      var n_1multiplier = (+i-1)*multiplier

      // add f(n-1) and n_1multiplier
      var result = +fn_1+n_1multiplier;
      
      // assign the result to the array
      resultarray.push(result)
 
      }
    }
   
  // return the last value in the array
  return resultarray.slice(-1);
  
}

---

Example data showing breakdown of calculations

Answer 3

You have defined the function in a recursive fashion. It is possible to implement the function directly, without either recursion or iteration.

If we expand the first six terms, we get:

If we examine the sequence of factors closely:

1,2,4,7,11,16,22,29,37,46,....

we notice that they are one more than the partial sums of a sequence of integers described here:

Wikipedia Article

The formula to generate our sequence is:

=1+n*(n-1)/2

(this is a variation of the Pythagorean formula shown in the Article)

So in either Excel or Google Sheets, if we put n in cell A1 we put in B1 the formula:

=5000*(1+A1*(A1-1)/2)

This should be valid for values of n greater than zero:

EDIT#1:

Here is the same implementation using Google Sheets: