excelexcel-formulaadditionself-reference

How do I add values to the same cell with its current value?


I am trying to add value of cell A1 to cell A2. However, I want to keep adding A1's value to A2's current value because I want to keep some monthly record and that is represented by A2. I will keep changing A1's value daily.

One way I found is the use of circular reference. But, the problem is, wherever I type something, It also gets added in the A2, which is weird and not required. How do I do this without any VBA because I want to use the formula in Google Spreadsheet.

I have also attached a demo GIF image. It shows that circular reference is not working well. Demo GIF


Solution

  • Your tags and question itself are misleading. You use Excel tags, show GIF of Excel, but want solution for Google Sheets. Note that Excel and Google Sheets may share most of the formulas, but these are different apps and you often need different solutions for various tasks.

    Anyway there is no solution for your task using simple formulas. In Excel you would need to use VBA, in Google Sheets - Apps Script. Since you want it in Google Sheets there are some ways you could do this. One way is to write a script using onEdit(e) trigger so calculation is made when certain cell is altered or you could add button which does calculation you want. Simple solution for latter:

    Add button and assign script to it:

    function myFunction() {
      var toadd = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getRange("A1").getValue();
      var tosum = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getRange("A2").getValue()
      SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getRange("A2").setValue(tosum+toadd);
    }
    

    Any time you press the button it reads A1, A2 values and sets A2 value to A1+A2

    Result:

    enter image description here