Sum Google Spreadsheet cells in a certain order

Question

I've looked and not been able to get my specific answer. What I'm trying to do is have a total owed cell, and an amount paid cell. I want the total owed to subtract the amount paid, and update itself to the new lesser amount, so that when I enter another amount in the same amount paid cell, it subtracts again from the previously updated amount. I've tried different formulas, but I keep ending up with a circular dependency error. Is there a way to have cells calculate in a certain order? Do cell a21 first, then do cell b21 using the updated value from a21, and so on.

Answer 1

You will need to use a VBA script to achieve what you need.

The code will need to be in the Worksheet_Change event handler for the sheet in question.

Essentially the code should use the following logic

1. Check that the cell being updated is the amount paid cell
2. Recalculate the total owed

3. Optionaly, clear the amount paid.

   Private Sub Worksheet_Change(ByVal Target As Range)

   End Sub

Answer 2

An onEdit() trigger function in Google Apps Script can do this easily.

Refer to "Spreadsheet Edit Events" in Understanding Events, and Understanding Triggers.

function onEdit(e) {
  // For simplicity, we'll use A1 notation to refer to cells & ranges
  var cellA1Notation = e.range.getA1Notation();

  // Check if a new payment was made
  if (cellA1Notation == 'B2') {
    // Make sure we're dealing with a number
    var payment = eval(e.value.valueOf())

    // Archive payment
    var pastPaymentRange = e.range.getSheet().getRange('B3:C3');
    pastPaymentRange.setValues([[payment,new Date()]]);
    e.range.clear(); // Clear payment

    // Update total payment
    var totalPaymentRange= e.range.getSheet().getRange('B4');
    var totalPayment = totalPaymentRange.getValue();
    if (typeof totalPayment !== 'number') totalPayment = 0;
    totalPayment += payment;
    totalPaymentRange.setValue(totalPayment);

    // Update balance
    var balanceRange = e.range.getSheet().getRange('B5');
    var balance = balanceRange.getValue();
    if (typeof balance !== 'number') balance = 0;
    balance -= payment;
    balanceRange.setValue(balance);
  }
}

Answer 3

You can avoid scripts and circular refs by changing strategy. Add a new row every time you get in/out money. Your formulas will just add up the total as in sum(a3:a) Plus you get historical data as a bonus.