Google Sheets – Converge

TIL: Moving average with missing periods in Google Sheets

The short answer:

=AVERAGEIFS(A1:A99, B1:B99,"<="&B2, B1:B99, ">"&B2 - 30)

Values to be averaged are in A1:A99, corresponding dates are in B1:B99.

What the formula does: average the values in the range – Include a value in calculating average for the current date if:

The date for that value is same as or before the date in the current row, and
The date for that value is greator than the date X days before the date in the current row (X is 30 in the formula for a 30-day moving average)

The long one:

I have a spreadsheet with my daily weight log. It has occasional missing days – when I didn’t log my weight.

Yesterday, I wanted to chart this data, and wanted to add a moving average to it. Google sheets’ in-built moving average trend line refused to work – either due to the missing data, or due to the number of entries. So I added a column to the sheet with the calculated (trailing) moving average weight.

I’ve never before had to calculate moving average over a non-consecutive data set. So, in case I forget, I’m noting it down here for later…

Continue reading TIL: Moving average with missing periods in Google Sheets

TIL: Conditional sum product in Google Sheets

=SUMPRODUCT(D5:D1009="",C5:C1009,B5:B1009)

Just learnt an easy way to do conditional SumProduct¹ calculations in Google Sheets².

In above example, I wanted the SumProduct of values in columns B and C, when the respective cell in column D was empty.

The solution is to simply introduce the conditional column as another input, with the condition stated in the input.

Sheets does the SumProduct calculations, with each true as 1, and false as 0, thus making it a Conditional SumProduct :)

Continue reading TIL: Conditional sum product in Google Sheets

How I read and respond to user feedback

After writing the previous post, I thought I’d share another script I use, this time to respond to user feedback for my Chrome apps.

All the feedback links in the apps, and the link on page that opens when they are uninstalled, direct to this form.

Chrome apps' feedback form — Chrome apps’ feedback form

The problem

On submission, the form adds a row to a spreadsheet with each of those input fields in a separate column. Google provides a notification option for whenever the form is submitted. However, the default email sent by Google is quite useless:

The default notification email requires a click-through to see the changes. This is sad enough on the desktop. On mobile, it’s completely useless – requiring me to open a big spreadsheet to see just one new row of data!

Continue reading How I read and respond to user feedback

How I automate fetching Chrome Web Store user counts & ratings

I have 7 Chrome extensions and apps and, as a chronic numbers addict, I like to keep track of their user numbers (WAU), and ratings.

I use a spreadsheet (Google Sheet) to collect the data, and analyze trends, and catch (to diagnose) outliers. The same spreadsheet also functions as a JSON-providing backend for data being funneled elsewhere (e.g. for user numbers on this page).

While the analysis part, and the JSON-feed worked well, the data collection part was painful. Google doesn’t provide an API to fetch extension data, so I’ve had to fill the data manually into the spreadsheet every day!

For a long time, I used to open my Chrome Web Store (CWS) developer dashboard every morning, and one-by-one fill in the numbers into the spreadsheet cells. While this was relatively easy, if menial, on the desktop, it’s quite painful on the phone – copying numbers between two apps on the small screen.

Continue reading How I automate fetching Chrome Web Store user counts & ratings