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

I have the following columns in Google spreadsheet

Date and Completed Tasks

Sample data includes

• May 17, 2016 3
• May 18, 2016 10
• May 19, 2016 9
• May 20, 2016 8
• May 21, 2016 9

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.

• How are defining moving average and variance? – Sun Jun 3 '16 at 2:12
• 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

## 1 Answer

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

Moving Average Google Sheets

Used Google searches to find:

1. 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)))`

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

`=ROW()-1`

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