0

I have tried to formulate a cell (e.g. A2) as it can be summed iteratively by another cell value (e.g. A1) when A1 is updated. The following formula is used using some previous SO answers:

=IF(A1<>"", IF(Total="", A1, Total+A1), "")

In which "Total" is referring to A2, and iterative calculation is enabled in formula option by maximum iterations = 1.
The solution must be as e.g. if A1 = 2 then A2 = 2. Then if cell A1 be updated to value = 5, then A2 becomes equal to previous A2 value + new A1 value i.e. = 7.
The issue is that when I am using this code, each operations on each other cells execute this code e.g. if A1 = 2, so A2 = 2 but placing a value in B20 (no matter which value) execute the code and A2 will be changed to 4 (2+2).

Is it possible to formulate the cumulative sum without VBA?

Ali_Sh
  • 2,667
  • 3
  • 43
  • 66
  • 2
    *Is it possible to formulate the cumulative sum without VBA?* Nope. Notice normal Excel formulas do not save values like VBA can do. Formulas just take the actual values involved. With VBA, you can use the actual value and add a new one, overwriting old one in same cell (cumulative) but not normal Excel formulas as far as I know. – Foxfire And Burns And Burns Sep 08 '21 at 11:53
  • You would be able to do this by adding a column and calculate the conditional total there. Otherwise you need VBA – P.b Sep 08 '21 at 12:44
  • Thanks @FoxfireAndBurnsAndBurns for the explanation. Besides the main question that you explained, is it clear what is happened in the written code above? Any changes in other cells will execute the code repeatedly. This code seems to work correctly if we have only the two cells or, in other words, only change the updating cell i.e. A1 here. I think, this raises the probability that the written code could be corrected with some tricks to give the true answer without any other problems as I described in the question. – Ali_Sh Sep 08 '21 at 12:46
  • Thanks @P.b for your contribution. I know those ways, but the purpose of this question is to use formulas beside referring or something like that. Based on the described reason in my last comment, I think it could be probable to reach the goal by a small tricky correction in the aforementioned written code, which I am seeking for. – Ali_Sh Sep 08 '21 at 13:00
  • The thing is, as soon if you write data in any cell containing the formula, the formula would be overwritten/gone in that cell. Therefore it wouldn't update that cel anymore. VBA van do that. Formulas simply can't. – P.b Sep 08 '21 at 15:12
  • @P.b Yes, it is the advantage of VBA than the requested solution. – Ali_Sh Sep 08 '21 at 15:32

0 Answers0