Cleaner formula for subtracting a set amount from a column of values each calendar day in google sheets?

Question

Formula: In cell A1 I manually type the date that I'm adding a new value, in cell B1 I have the Today() formula, and finally in cell C1 I have the formula =YourStartingValue - (5*(B1-A1)).

Result: The end result is that the value I input into cell C1 gets 5 subtracted from it each new calendar day. I then repeat this step in a new row for each new value that I add.

My question: is there a formula I can use so that I don't have to add the stuff in A1 and B1 each time? It'd save time and make things cleaner. Thanks!

Excel automatically copies down formulas from preceding rows if the data set is assigned to a table. — Ralph
Is there not a singular formula I can use for this? Also, I'm using google sheets. — user6432128
You can combine all into one formula = =YourStartingValue - (5*(TODAY()-DATE(2016,1,1))) where DATE(2016,1,1) is the date you have in A1. As far as I can tell the value in A1 is not predictable and therefore has to be entered each time. — Ralph
Thanks Ralph, that worked like a charm! One final semi-related question before I go; is there a way to auto update the cell with the current date whenever it gets edited? I found this script, but I'm not sure how to extract note info into the cell formula, or how to restrict this google script so that it only adds date-modified notes for 1 column instead of the whole sheet. — user6432128
I wouldn't know how to do such things in a google-spreadsheet (if that's possible). But maybe any of the other members here can help you with that. — Ralph

Unknown Unknown · Accepted Answer · 2016-06-07T01:27:24

In order to have 5 subtracted from every value in some column every day, I would use a script such as this one:

function subtractDaily() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1'); 
  var range = sheet.getRange('C:C');
  var values = range.getValues();
  range.setValues(values.map(function(row) {
    return [row[0] == parseFloat(row[0]) ? row[0] - 5 : row[0]];
  }));
}

Change the sheet name and column as needed, and set it to run daily using "Resources > Current project's triggers" dialog of the script editor.

The script checks that the cells contain numbers and does not attempt to subtract 5 from text or blank cells.

Cleaner formula for subtracting a set amount from a column of values each calendar day in google sheets?

1 Answers