# Google sheets average between cells

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? 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() {
}

function interpolate() {
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] !== '') {
break;
}
}
for (var j=row+1; j<height; j++) {
if (values[j-1] !== '') {
break;
}
}
var a = values[i-1];
var b = values[j-1];
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? – okay56k 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? – okay56k 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. – okay56k Jul 23 '15 at 0:43