Google Sheets – Create Recursive Formula in Single Cell

google sheets

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?

Best Answer

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.

Related Topic