Google Sheets – Using Sigma Notation to Calculate Series

Google should now respect X-axis date values for Line, Area, Scatter, and Time Line charts.

For example, if you have the following data*:

|    Date    | Value |
|------------|-------|
| 01/01/2016 |   3   |
| 02/01/2016 |   4   |
| 03/01/2016 |   5   |
| 03/15/2016 |   6   |
| 09/01/2016 |   3   |
| 12/01/2016 |   3   |

^{*where the date values are stored as dates. If not, you can use =DateValue() to convert a date string to a date value.}

You'd should see a lot of data in the beginning followed by a lull of activity in the middle of the year.

Area, Line, Scatter, and Time Line (located in Other) should all do so:

By contrast, bar charts continue to treat date values as a simple string and return each of the values sequentially:

And here's a public spreadsheet with all of the included examples

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.