0

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?

  • Welcome. Please read how to and share a test sheet so as you can be easier helped. – marikamitsos Jun 17 at 3:47
  • @marikamitsos I added a test sheet. – Austin Headley Jun 17 at 3:56
  • Since your explicit declaration of F(1)=5000 does not follow your declaration for F(n) (which should return 0 for n=1 and 5000 for n=2) I'm assuming that means you'll be individually defining F(0) and F(1) in cells without a formula. In other words, the formula F(n) doesn't need to accept any n value less than 2. Is this correct? – Kwilinsi Jun 22 at 1:55
  • @Kwilinsi Yes, 0 would never be an input so f(0) would always be 0 simply because I would never input 0 into this function. F(1) is a value I would define in a separate cell, so in essence, I only care about values for n that are 2 or higher. – Austin Headley Jun 23 at 2:07
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
  1. 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)
  1. 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)
  1. 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.

| improve this answer | |
  • =arrayformula(sequence(n,1,0)*5000) is an elegant timesaver. May I suggest that you need to turn the solution into a free-standing formula with no external dependants. Something like the following formula in cell B1 and "n" in A1: =if(A1=0,0,if(A1=1,5000,5000+sum(arrayformula(sequence(A1,1,0)*5000)))). – Tedinoz Jun 22 at 9:52
  • Because I gave a very general usage of my needs, I had to do a tiny bit of modification to make this work for my problem. However, this is exactly the correct answer and works perfectly for what I needed. Thank you so much for putting the time in to work this out in such detail that I could understand. – Austin Headley Jun 23 at 2:26
0

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

Screenshot

| improve this answer | |
  • The solution by @Kwilinsi is much more efficient that this answer. – Tedinoz Jun 22 at 9:55
  • Thank you for the answer. This absolutely would have worked for me, and is what I originally was afraid I would have to do. I was just hoping there was a native method in Sheets like @Kwilinsi answered so I wouldn't have to achieve this with scripting. I thank you for your time and effort though. – Austin Headley Jun 23 at 2:28
0

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:

enter image description here

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:

enter image description here

EDIT#1:

Here is the same implementation using Google Sheets:

enter image description here

| improve this answer | |
  • This question is about Google Sheets but your screenshot looks to be taken from Excel. Have you tried your solution in Google Sheets? – Rubén Sep 17 at 19:51
  • @Rubén You are correct! I will try it and update my Answer..................thank you ! – Gary's Student Sep 17 at 20:24
  • @Rubén Please see my EDIT#1 ..................thanks again! – Gary's Student Sep 17 at 20:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.