Google-sheets – How to automatically fill missing data in Google Spreadsheet

google sheetsgoogle-sheets-arrayformulagoogle-sheets-charts-trendlines

I'm searching for the exact same thing as Google Spreadsheet: How to fill in missing values (inbetween filled values) automatically

I made a spreadsheet for tracking weight, where each row is one day, and one column is weight. Although I've had Google Spreadsheet create a row for every date in the year, I only measure my weight a few times a week. What I would like is a formula to tell Google Docs to use "the last manually filled entry higher in the column", or something along these lines.

Ideally, the last filled row though, will be a manual entry (i.e., hopefully I could paste the formula into all cells in the column, and blank rows between manually-entered rows are filled, but nothing after the last manual entry).

Even more ideally, between two manually filled rows (i.e., row 10 and row 15), the formula would be able to interpolate between the two, but I realize that is asking a bit much.

I have no experience in making these formulas though, can anyone point me to somewhere to start to solve this problem?

But the solution provided doesn't seem to work for me.

Can someone help me?

Best Answer

There's multiples ways to do this :

I used =Trend but =Growth works too.

=TREND(C2:C11,A2:A11,A12:A24)

=Forecast is an amazing function too.

=FORECAST(A3,$B$2:$B$11,$A$2:$A$11)

You can use Arrayformula for extra lazyness :

=ARRAYFORMULA(FORECAST(A3:A24,$B$2:$B$11,$A$2:$A$11))

Theses formulas will be accurate the more data you feed them.
I updated your sheet. Similar question on stackoverflow.

Related Solutions

Google-sheets – How to calculate weighted averages in Google Sheets

There is no built in function to calculate the weighted average so you have to write a custom function if you want to avoid using too many temporary cells. So here is how you can achieve your goal.

Go to Tools > Scripts > Script editor..., Copy/Paste the bellow code and save it:

function weightedAverage(v, w) {
  var sum_v = 0;
  var sum_w = 0;

  if (v[0].length != w[0].length) {
    return "#ERROR: Incorrect number of values and weights";  
  }

  for (var c = 0; c < v[0].length; ++c) {
    sum_v += v[0][c] * w[0][c]; 
    sum_w += w[0][c]; 
  }

  return sum_v/sum_w;
}

Use it like:

=weightedAverage(B3:D3,$B$2:$D$2)

Where B3:D3 are your values and $B$2:$D$2 your weights. It's not error proof (the only check is to make sure both arrays have the same length) but it will do the trick.

In the above example I'm not trying to extract the weights from the title of the attribute, but I'm reading them from the second row (B2:D2) to make our life easier and clearer. The $ is not changing the result of the formula. It's only affecting what's happening when you're copying the formula in another cell. The part of the cell reference following the $ will not change (link for more details). Write the formula once in the cell E3 and Copy it to the rest of the rows to see it in action.

Google-sheets – Unique Values From a Matrix

Possible but may require some 'compromising':

=arrayformula(right(split(FILTER(B3:B, A3:A>0, LEN(B3:B)>0), ","),1))

in D1 to F10 does find the 2 (which should have been 'underneath' where you put your formula for the purposes of your example) - it also strips out Value, though this could be added back later if required.

in say G1 and copied down to suit:

=arrayformula(LARGE(value(D$1:F$10),row()))

then with all your values in a single column, in say H1 and copied down to suit (upto #NUM!):

=unique(G:G)

The results will be in reverse order from as shown by you (sort?) because of use of LARGE rather than SMALL because if positive values only all the 0 would have come first and there are quite a few of those.

Concatenate results with ="Value "&H1 etc if required.

Best Answer

Related Solutions

Google-sheets – How to calculate weighted averages in Google Sheets

Google-sheets – Unique Values From a Matrix

Related Topic