Google Sheets – How to Find Average of Difference Between Two Columns

formulasgoogle sheetsgoogle-sheets-arrayformula

Imagine you have a Google sheet with two columns, each holding a time – let's say one is bus departure and the other is bus arrival.

Is there any elegant way to present the average of all the time the bus spent on the way each day?

Example:

Departure     Arrival     Average_Time_in_Transit
13:00         14:00       1:00
13:15         14:15
12:15         13:15
12:00         13:00

Best Answer

=ARRAYFORMULA(IF(LEN(A2:A), TEXT(B2:B-A2:A, "[h]:mm"), ))

=TEXT(AVERAGE(ARRAYFORMULA(IF(LEN(A2:A), B2:B-A2:A, ))), "[h]:mm:ss")

=ARRAYFORMULA(IF(LEN(A2:A), TEXT((B2:B-A2:A)/COUNTA(A2:A), "[h]:mm:ss"), ))

Related Solutions

Google-sheets – subtract a cell value in another cell that already have one in Google Spreadsheets

As Google Sheets does not natively support iteration with circular referencing, you will need to resort to Google Apps Script. Eg:

function onEdit(e)
{
  var editRange = e.range;
  var sheet = editRange.getSheet();
  if (sheet.getName() == "Sheet1" && editRange.getA1Notation() == "B1")
  {
    var updateRange = sheet.getRange("A1");
    var newValue = updateRange.getValue() - e.value;
    updateRange.setValue(newValue);
  }
}

Google-sheets – How to use row values in conjunction with columns

In C1 and copied down to suit:

=sum(A1:B1)

SUM

Or without copying down:

=ArrayFormula(A1:A+B1:B)

Best Answer

Related Solutions

Google-sheets – subtract a cell value in another cell that already have one in Google Spreadsheets

Google-sheets – How to use row values in conjunction with columns

Related Topic