How to use Google Spreadsheets to chart completed tasks using time series graph?

Question

I have the following columns in Google spreadsheet

Date and Completed Tasks

Sample data includes

I want to create a time series chart and to see moving average, variance plotted on it.

How do I do that?

Tasks will always be integer.

Moving average is take all the total tasks completed up to that day and then divide by days up to that date. As you add more data, the average on different dates should be different. — Kim Stacks, Jun 4 '16 at 1:29

Community · Accepted Answer · 2017-03-20 10:17:56Z

I assume you know how to make a chart where the X is date, Y is your moving average (if you have the value).

Used Google searches to find:

How to sum up rows above - modified to select previous column and to include current row

=SUM(INDIRECT(ADDRESS(1,COLUMN()-1)&":"&ADDRESS(ROW(),COLUMN()-1)))

Row function - Row function used to display current row number minus 1 to adjust for a row header

=ROW()-1

Divide SumRowsAbove with RowNum (C/D) which returns your definition of MovingAverage (E).

You can simply lock part of the cell using sum instead of using indirect, row and column. So using the formula =sum(B$2:B2)/(row()-1) in E2 will give the same result. — Jerry, May 3 '18 at 10:05

1 Answer 1