How do I set up my Google Sheets document so it does what I want?

June 12, 2017 9:25 AM

I'm trying to make a spreadsheet that tracks my progress in my rock climbing hobby, but I am pretty clueless about spreadsheets and how to set them up/make them useful. Details of what I need inside!

Here is what I want: The date I climbed, the ratings of every route I attempted that day (V1-9 if bouldering or 5.6-5.12+ if climbing walls), and whether I was able to finish the route or not (a binary, like YES or NO). I would like to eventually be able to make some sort of graph or chart to see my progress (which I also don't know how to do). Can anyone explain how to set that up/format that? TIA!

posted by carlypennylane to Technology (4 answers total)

How do you want to enter this info? Because you could just open up a spreadsheet and start typing in the cells, that works just fine. Or you could create yourself a Google Form to use for entry so that it looks more like a web form (e.g. you enter the date via a calendar like when you make an online booking rather than just typing "6/12/17", or you have a dropdown that has all the ratings in it and you just select from that). That's easy enough to do. There's lots of how-to videos out there, like this.

As far as making charts and graphs, that's also pretty easy, and there's also a lot of tutorial videos available. (You may notice a theme with these videos--teachers use these tools a ton because they are free, and user-friendly.)
posted by soren_lorensen at 10:11 AM on June 12, 2017

Response by poster: Thanks for those resources! I don't mind entering the data into cells and I don't need it to look pretty, but I'm just not sure HOW to enter it so future charts can analyze the data the way I want. Do I enter date as the top of a column, with the route numbers for that day below it? How do I add the third variable of whether I finished the route or not? Does color coding show up on a chart (green = finished, red = didn't finish)? Or do I need to enter that data point in a separate cell?
posted by carlypennylane at 11:52 AM on June 12, 2017

It depends on what you want to analyze with the charts (though once you've got data in there, it's easy to copy/paste it in different orders, sort the data in various different ways, or tell the chart to use different data, or the same data but reverse the axes). Do you want to look at frequency? An change in a variable over time?

The path of least resistance here will be your dates in column A, the route ratings in columns B & C (and I'm going to suggest you have different columns for bouldering vs. climbing wall since they are different rating systems and if you put them both in the same column, your charts won't know the difference between a V6 and a 5.8--it's all just numbers to a spreadsheet). And yeah, go with a separate column for finished/not. YES/NO is fine--you can do a frequency bar chart with that that will count up the number of Yesses and the number of Nos and represent that as a bar chart. (You could also consider two separate sheets for bouldering vs. climbing wall so as not to muddy the waters with the different ratings systems.)
posted by soren_lorensen at 12:25 PM on June 12, 2017

Soren_lorensen's got it, and just to be clear, you will want each row to represent one attempt. So, each row will have a date (column A), A wall rating (column B, leave blank if it was a boulder problem), a boulder rating (column C, leave blank if wall route), and Yes or No (column D) for completion.
posted by Ausamor at 1:24 PM on June 12, 2017