Google docs spreadsheet shortcomings

Published January 12, 2014

I've been using Google docs spreadsheets to log my runs since about September. I just wanted something to record when I ran, how far, how fast, etc, give me somewhere to note down anything of, er, note, and generate a few weekly/monthly summaries.

It is useful and I like the fact it has let me do some things I didn't consider to begin with, like creating a report telling me the mileage of my shoes, but then it makes other things frustrating. Things that don't seem like they should be too hard.

1. I want the ability to sort my pivot tables strangely.

Let's say I have a have a pivot table that lists my running shoes and displays their mileage. I don't want it sorted by name. In a few years I might well have gone through 10 shoes and retired or almost-retired 7 of them. So I want it sorted by which shoe was used most recently, which would be possible were I able to sort by an expression.

2. I want to be able to use the data calculated by a pivot table in the same pivot table. If I have columns A and B in a pivot table, I should be able to create a column C, using A and B

Let's say I want to see a weekly average of my mile pace. If one week I run a mile at 7:00 pace and a marathon at a 9:00 pace, my average is not 8:00/mile, but the pivot table will average them so. If I want a proper average, I have to add a lot of redundancy into my data set to sum the number of miles for that week, the total time for that week, and then divide the two to determine the real average pace. I am using a sumif() in my raw data to group by week, even though the whole point of the pivot table is to group by week! Then I add the pace field to the pivot table, so I can see it there, aggregated by min/max/avg (they should all be the same).

This adds three extra fields in every single record in my dataset, when in reality all I wanted was to be able to add a new field =A1/B1 or something in my pivot table.

I also have a group-by-month pivot table. So now I need another three fields...

Filed under: technology, internet, spreadsheets

Talk is cheap

Leave a comment:

HTML is not valid. Use:
[url=]Google[/url] [b]bold[/b] [i]italics[/i] [u]underline[/u] [code]code[/code]