ArrayFormula that refers to the previous row? (Circular dependency error)

Question

I'm having trouble with what seems like it would be quite a common requirement: an array formula calculation that incorporates prior rows. The issue seems to be that all cells in the array formula are evaluated at once, and so it thinks there's a circular dependency, and gives that error.

I've mocked up a toy example on Google Spreadsheet which you should be able to see and comment on. (I've disallowed edit access to prevent vandalism.)

It looks like this, where everything is in row 2, and bullets 1,2,3,4 refer to columns A,B,C,D respectively:

1. 'Change' - blank for manual entry
2. 'Ex. interest' - =ARRAYFORMULA(IF(ROW(B2:B)>2, OFFSET(D2:D, -1, 0) + A2:A,))
3. 'Interest rate' - blank for manual entry
4. 'Balance' - =ARRAYFORMULA(IF(ROW(D2:D)=2, 0, B2:B*(1+C2:C)))

The idea is to have the interest compound (with a possibly variable rate) and the option of a plus/minus capital change on each row, however, cells B2 and D2 (points 2, 4) error #REF, and on hover report a 'circular dependency'.

I've tried everything I can think of: OFFSET, INDIRECT, D1:D (instead of 2), even a helper column to do nothing but refer to the previous row of D (that just caused a three-column circular dependency instead).

How can I have the 'ex. interest' column refer to the 'balance' column of the previous row in an array formula without causing this error?

Similar questions solve this with SUMIF over the rows, conditioned on the row being less than the 'current' row in the array formula. I can't see that this could work in my case, since I would need to multiply the whole running sum by the interest at each step. I tried SUMIF(... "="&DATE(...) ...) to refer to the previous single row; this works in a helper column to print the offset balance, but trying to use it (with iterative calculation on per below) just behaves like it's zero.

At @JackBrown's suggestion, I enabled iterative calculation which rids the circular dependency error, however it still doesn't work - the second filled row gets a value of 0 from the previous row, despite the previous row having a non-zero value. As if it took the value on an early iteration, and didn't update on change.

Answer 1

Excel

In excel make smart table and use formula:

=If(row()=2,A2*(1+B2),(A1+C1)*(1+B2))

Please download sample file here: https://drive.google.com/file/d/0B79ClRnKS87QTTR5VDBld0plajg/view?usp=sharing

---

Google Sheets

I've made a research in order to try solving your problem. Short answer: I can't do this and I believe that's impossible.

Background

I simplified the case to the task of finding future rate value with changing rates:

Rate+   Recursive formula for rate
1.00%   101.0000%    =(1+A2)
1.50%   102.5150%    =B2*(1+A3)
1.10%   103.6427%    =B3*(1+A4)
1.10%   104.7827%    =B4*(1+A5)

Then I've made an array by hand to get the same result and got this:

={1+A2;
  1+A2+(1+A2)*A3;
  1+A2+(1+A2)*A3+(1+A2+(1+A2)*A3)*A4;
  1+A2+(1+A2)*A3+(1+A2+(1+A2)*A3)*A4+(1+A2+(1+A2)*A3+(1+A2+(1+A2)*A3)*A4)*A5}

As you see, the number of arguments grows very fast:

1  = 2 ^1 - 1   1+B2         
3  = 2 ^2 - 1   1+B2+(1+B2)*B3 
7  = 2 ^3 - 1   1+B2+(1+B2)*B3+(1+B2+(1+B2)*B3)*B4   
15 = 2 ^4 - 1   1+B2+(1+B2)*B3+(1+B2+(1+B2)*B3)*B4 + ... 

And to calculate the result for final row we need 2^n - 1 variables:

• for row 32 this number is 4 294 967 295!

That's why arrayformula can't handle calculations even when you enable Iterative calculation= On

---

Workaroud But this is very simple task for recursive functions or scripts, because they can remember the last found value. So I suggest writing script and use it as custom arrayformula:

function futureValue(values, rates) {

  var result = [];
  var resultCurrent = 0;

  for (var i = 0, len = values.length; i < len; i++)
  {
    resultCurrent = (+values[i] + resultCurrent) * (1 + +rates[i]);
    result.push(resultCurrent);
  }

  return result;

}

Links:

1. Sample file with the script working
2. Guide on custom functions