1

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

1

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);
  }
}
  • That's a really cool solution, I didn't know you could write Javascript functions in Google Sheets. This function just fills everything in with zeroes though? – 56kNULLMODEM Jul 23 '15 at 0:34
  • My bad, it's a rounding error. I'm dealing largely with decimal places. Can the JS be amended to deal with floats and not just integers? Or do I have to multiply and divide? – 56kNULLMODEM Jul 23 '15 at 0:37
  • This is what it does for me. Check that your column isn't formatted to show just the integer part? – user79865 Jul 23 '15 at 0:41
  • Oh, from your screenshot it looks like the numbers are formatted as text: they are left-aligned. That is definitely a problem. Numbers should be numbers, otherwise you can't calculate with them. – user79865 Jul 23 '15 at 0:42
  • You were right, bad formatting. Thanks so much! Terrific answer. – 56kNULLMODEM Jul 23 '15 at 0:43

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.