My employer, Pearson, provided the opportunity for its employees to participate in the 2011 Global Corporate Challenge. The GCC consisted of teams of 7 who tracked their daily steps counted with a pedometer for 16 weeks. The idea is to motivate people to become more active. The event started on May 19, 2011 and ended September 6, 2011. Being an engineering type of person, I liked the idea of gathering the daily step count as a metric for activity. The event suggested a goal of achieving 10,000 steps a day. I was pretty active recording my pedometer activity every morning via the SMS text message data entry technique: the GCC has an SMS short code and you entered the date and number of steps. Submitted text messages were recorded in your step log on the GCC web site. Being able to enter the data via my phone was very handy as it let me enter data while I was on vacation without missing a day. But, now that the event is over, I still wanted to keep track of my step counts. Here is the technique that I worked out using Google Docs to record my step counts.
I created a Google Docs Form to collect my daily step count. The collected data is stored in a Google Docs Spreadsheet. The form can be viewed in a web browser or in Google Docs for Android. Since I have an Android phone, the form lets me use my phone to still collect my daily step counts each morning. And since the data is stored in a spreadsheet, I can perform calculations on the data to get my total and average step counts and even graph my daily totals. Here is the process that I use.
I first created the form. From Google Docs, I selected “Create new” > “Form”. This opened the form editor. I entered the form title of “Step Entry” and description of “Daily step count entry”. My form currently has two questions; “Date” and “Steps”. The first question, “Date”, has help text of “Date steps were accumulated”, it is a “Text” question type, and the question is required.
Step Entry - Edit Form - Date Question
The second question, “Steps”, has help text of “Number of steps accumulated”, it is a “Text” question type, and the question is required.
Step Entry - Edit Form - Steps Question
Once this form is saved and created, it is linked to a spreadsheet. I renamed the sheet “Data”.
Step Entry - Data Sheet
I created a new sheet and named it “Statistics”.
Step Entry - Statistics Sheet
On the “Statistics” sheet, I added the “Total Step Count”, “Daily Average”, and a time line trend chart.
For the “Total Step Count”, the formula used was pretty simple: =sum(Data!C2:C109). Basically, the “Steps” column in the “Data” sheet are summed together.
For the “Daily Average”, the formula is a little more complex: =sum(Data!C2:C109)/countif(Data!C2:C109,">0"). First, the “Steps” column is summed and then divided by the number of “Steps” cells that have a number greater than zero. There is an “AVERAGE” function, but it would include cells that are zero (or don’t have a step entry). I found the technique to calculate the correct average from this web page: Excel Average Formula/Function Without Including Zeros.
The chart is a time line trend chart. The “Data” used for the chart was: Data!B2:B109, Data!C2:C109.
As new steps are entered, the statistics are kept up to date.
At this point, the web portion of the technique is complete. But I wanted to be able to add data through my mobile phone. My phone is an Android phone and I could use the Google Docs for Android app to directly edit the spreadsheet. But using the Step Entry form can provide a more “app-like” experience. I got the Step Entry form URL by selecting “Form” > “Embed form in a webpage…”. A new window was opened with a dialog that had the URL to paste into a “blog or website”. I copied the whole “iframe” code and pasted it into a text editor: <iframe src="https://docs.google.com/spreadsheet/embeddedform?formkey=foo" width="760" height="623" frameborder="0" marginheight="0" marginwidth="0">Loading...</iframe>. I copied the “src” attribute value, https://docs.google.com/spreadsheet/embeddedform?formkey=foo, and pasted it into a new Chrome window. I then used Chrome to Phone to send the form URL to my phone. The URL opened on my phone in the browser. I bookmarked the URL. Then I added the bookmark as a shortcut on one of the phone’s home screens. To do this, I “long held” my finger on the home screen and the “Add to Home screen” dialog was displayed. I selected the “Shortcuts” option. From the “Select shortcut” dialog I selected “Bookmark” option. I then selected the “Step Entry” bookmark that I had previously created.
Step Entry home screen shortcut
Now, when I want to enter my daily step entry, I select the “Step Entry” shortcut. The first time I select the bookmark a dialog allowing me to complete the action using “Browser” or “Docs” is displayed. I can optionally choose to select the checkmark to use my selection by default.
Complete Step Entry bookmark action
I used the “iframe” URL for the “embeddedform” so that there is no extra formatting. Since I am using the form on my phone’s smaller screen, not having the extra formatting actually makes the form easier to use. I also like the layout of the form when opened in Docs better than the Browser so I choose to complete the action using “Docs”. Here is the Step Entry form that I can use to enter my daily step total.
Step Entry data entry form
I enter the date, total number of steps from my pedometer, and then press “Submit”. The data is uploaded to the Google Doc spreadsheet.
This process is very convenient and easy to do. It allows me to continue tracking my progress. Entering the steps daily also helps keep me motivated.
Thank you for this detailed info on recording step count! I, too, wanted to keep on logging my step counts after GCC ended, and what you have here is perfect. Thank you!!