Google Sheets – How to Calculate Average Between Cells

google sheets

I want to fill in the blanks between these cells with the average difference: i.e., because there are 11 blank cells between 0.234 and 0.334 the first blank should be 0.234 + ((0.334 - 0.234) / 11). How can I write a formula to fill in those gaps automatically?

Average

Best Answer

In principle, this could be done without a script... but it's really ugly. Already looking up the nearest nonempty cells (up and down) is nontrivial (and slow), and it gets much worse when you start writing data into the column, thus changing what's empty or not (circular dependency). One could calculate in another column and then paste the values... but it's better to just use a script.

The script below adds a custom function "Interpolate" to the menu, when the document opens. Place the cursor to any cell within a gap, and use the menu command.

Although the function interpolate() looks long, the logic is very straightforward. Among the advantages of a script solution is robust handling of edge cases: there is no data above, there is no data below, the data isn't numeric, or the gap doesn't actually exist. 

function onOpen() {
  SpreadsheetApp.getActiveSpreadsheet().addMenu("Custom", [{name: "Interpolate",  functionName: "interpolate"}]);
}

function interpolate() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var height = sheet.getDataRange().getHeight();
  var cell = sheet.getActiveCell();
  if (cell.getValue() !== '') {
    return;
  }
  var row = cell.getRow();
  var column = cell.getColumn()
  var values = sheet.getRange(1, column, height, 1).getValues();
  for (var i=row-1; i>1; i--) {
    if (values[i-1][0] !== '') {
      break;
    }
  }
  for (var j=row+1; j<height; j++) {
    if (values[j-1][0] !== '') {
      break;
    }
  }
  var a = values[i-1][0];
  var b = values[j-1][0];
  if (typeof a == 'number' && typeof b == 'number') {
    var insert = [];
    for (var k=1; k<j-i; k++) {
      insert.push([a+(b-a)*k/(j-i)]);
    }
    sheet.getRange(i+1, column, j-i-1, 1).setValues(insert);
  }
}
Related Topic