1

In Google Drive I'm keeping a spreadsheet of everything I spend money on. I have a heading for the cost but alone that's insufficient.

For example, if I tip after a meal I really want to have that amount separate. Right now I've been inserting such information as a note or comment but this isn't good because now I can't include it in any calculations. For example if I want to know how much I spent in a week it won't include any of the costs mentioned in the comments.

What's a better approach? I could add another column for Additional expenses and leave it blank if there aren't any. The problem with this is there may be more than 1 additional expense, for example bottle beverages have two government surcharges.

In a nutshell, how can I add data to a Google Spreadsheet that

  1. can easily factor into calculations such as max, average, sum etc.
  2. can have none, one or more than one values per row
  3. can have a money format
2

I would have a =SUM column that shows the total expense for each line. The sum is made up of all the following columns. The number of amounts on each line can be different, so that you can record tips, sales tax, or other amounts when they are applicable. Attach a note to these amounts describing what they are.

  | A           | B           | C          | D               | E               | F
1 | Date        | Description | Total      | Nominal charge  | Extra charge 1  | Extra charge 2        | ... | Extra charge X
2 | 12/01/2013  | Lunch       | =SUM(D2:2) |    $30          |  $5 (Note: Tip) |  $2 (Note: Sales tax) |
3 | 12/02/2013  | Dinner      | =SUM(D3:3) |    $50          |  %8 (Note: Tip) |                       |

So when recording a new expense, simply copy the formula from the previous row.

Note that the SUMfunction is open-ended, it will sum all numbers from and including column D. This means that all columns right of column C should be numbers - the description, date, and other information you want to record, must be put in columns left of column C.

If you should want to only record the total expense, simply write the amount in the SUM column.

If you want to automize the formula insertion (instead of dragging it down to every new row), you could use a script similar to the one listed here. Then, when you insert a new row, the formula is automatically inserted for you.

When the following script is installed, the formula from row 2 is copied and inserted when you insert a new row, adjusted for the new row number:

var FORMULA_COLUMN_IDX = 2; // The index of the formula column. 2 is column C.

function onModification(event) {
  var sheet = event.source.getActiveSheet();
  if (event.changeType == "INSERT_ROW") { // <-- This is true when a new row is inserted
    var templateFormula = sheet.getRange(2, FORMULA_COLUMN_IDX + 1).getFormulaR1C1();
    fillEmptyRows(sheet, templateFormula);
  }
}

function fillEmptyRows(sheet, templateFormula) {
    var range = sheet.getRange(1, FORMULA_COLUMN_IDX + 1, sheet.getMaxRows(), 1);
    var formulaColumnFormulas = range.getFormulasR1C1();
    var formulaColumnValues = range.getValues();
    for (var i = 2; i < formulaColumnValues.length; i++) { // Loop through all rows, starting at row 3
      if (formulaColumnFormulas[i][0] == "" && formulaColumnValues[i][0] == "") { // The formula cell is empty, so insert the formula from row 2
         formulaColumnFormulas[i][0] = templateFormula;
      }
    }
    range.setFormulasR1C1(formulaColumnFormulas);
}

To install the script, click Tools -> Script editor, and select Spreadsheet. Paste the above code in the script editor window. The script must be installed as an event handler: Click Resources -> Current project's triggers -> Add new trigger, select the onModification method and select On change from the right-most dropdown menu:

Screenshot of the Add Trigger dialog

OK, this was a long explanation, but I have set up an example spreadsheet to demonstrate.

1

tl;dr : Use a layout such as:

WA53364 example


Decades before you were born there were armies of comptometer operators who specialised in fast and extremely accurate arithmetic. In those days ‘scheduling out’ in ‘landscape’ format was common and a single row might span half a dozen sheets. The plethora of columns would be added across and added down and cross cast in an exceedingly tedious process.

Highly labour intensive, the objective was to minimise the drudgery and potential for inaccuracy by minimising repetition, in particular for professionals such as accountants and engineers who created the schedules – even if at the cost of shunting work on to long-suffering (but well paid) nimble-fingered clerks.

Such precedent seems still to be influencing, to some extent, the choice of layout nowadays. And also, of course, the end objective (of which we may be more conscious when starting out than of the steps required to achieve it) is quite likely to be a landscape format summary . However what has changed is that 33 years ago IBM brought its model 5150 and very many generations (in computer terms) later we almost all have access to a machine that thrives on repetitive calculations – indeed in some ways the more repetitive the better.

So for high data volumes the layout is always flat file and though this could be a few records and a lot of fields for big data may be hundreds of million of records but never millions of fields.

We all at times have need for matrix recording (eg timesheets) – where the position of the data is significant (eg a weekend) but almost all of us will also get to learn at some point, probably through bitter experience, that a tabular form is much more versatile. Pivot tables and other techniques are quite easy conversions from tabular to matrix (I am not denying the value of that for output) but the inverse is generally a greater problem.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.