Daily mileage chart - a How-to - Rejuvenation? Or Last Hurrah?

Daily mileage chart - a How-to

Details on how I created the daily mileage chart

IN THE PROJECTED ITINERARY page of this journal I posted a column (bar) chart showing the mileages I plan to cover each day. Another CycleBlazer posted a comment asking how I had done that. This entry amplifies and extends my response to the question.

My planning spreadsheet (a Google Sheets document, but the same thing can be done with Excel or Open Office, if you have those tools available) is set up with each row being a calendar day (riding or not), and one column as the day's planned mileage.

Part of my planning spreadsheet. Each row is a day; column G ("Planned Miles") contains the number of miles I expect to ride on any given day. Rest days are included, and get a 0 for the daily mileage, so that they can be counted and show as gaps in the final chart.

In the example data above, the yellow row is a rest day. It still shows up in the chart as a "gap", which is what I want, because I included the row in the data. If you omit days entirely the chart will not "fill in" missing dates and the rest day mileage gaps will not show.

The next step is to create a chart, using the daily plan as the data range. Column B will provide the data / labels for the horizontal axis, and column G is the data range that provides the mileage value for each day.

Google will take a guess as to what data you want to include in the chart, but it will probably guess wrong.

Google's guess as to what data should be in the chart will almost certainly be wrong, but they give you the means to correct their error.

The first step in correcting the error is to change the Data Range. Choose all of the columns between where you have the date and where you have the mileage; in my example that will be columns B through G. (You can, of course, organize your planning columns in whatever way makes sense to you.) Choose all the rows that have your daily data, but do not include any rows that summarize them (as for example a Totals row that counts total days and riding days, and sums mileage for the trip as a whole).

The area bounded in red is where you make changes to include the data rows and columns you need for your chart.

My spreadsheet has daily rows from row 1 (headings) to row 87 (last day of riding), so the data range for my chart is B1:G87.

The data range has been updated to include the data I want in the chart.

Note: including the row that has column labels in it (row 1 in my spreadsheet) enables Sheets to determine that these are the labels for potential data series. It will use those pieces of text as names in the subsequent steps.

Having set the data range, next set up the X-Axis. I chose "Calendar Day", which tells Sheets to use the values in that column as the markers to be spread along the X axis.

Choose the range that contains the dates in your plan.

When you choose the X-axis values, the chart updates itself but is not finished yet.

After the X-axis range is chosen the chart updates itself accordingly. The Y axis (values) still need to be set.

Set the data (Y-axis) values by removing the ones you don't want from the "Series" area. I removed all series (including "Calendar Day") except for Planned Miles.

Under "Series" you want ONLY the column that contains your daily mileages. Get rid of all the others.

After the unneeded data ranges are removed, the chart is almost done.

The last steps are to update the chart title and Y-axis labels. This is done using the "Customize" features in Google Charts.

While editing the chart you can click or double-click the chart title to open that part of the editor. Enter the text you want for the chart title.

Select the chart element you want to edit in the "Chart and Axis Titles" accordion panel and set / select the configuration you want for that element. The options may vary depending on which element you choose.

Choose the element you want to configure, then complete the information below from the options presented.

You can make additional tweaks and changes to the chart's appearance, to suit your tastes and needs. Determining how to do that is "left as an exercise for the reader", as many math texts say when presenting a theorem without its accompanying proof(s).

Your final chart may look something like this:

The finished chart. There are many more options to customize the appearance- play around and see which ones work best for you. Pro tips: CycleBlaze scales images down when they are uploaded, so start as big as you can so that the scaling doesn't harm the legibility. To make the axis labels legible, maximize the size of the chart by placing it on a tab of its own and displaying the tab in full screen mode on the largest / highest resolution monitor you have available. Also, make the axis label fonts fairly large- bigger than seems necessary to see them on your monitor.

Rate this entry's writing	Heart	1
Comment on this entry	Comment	4

Kelly IniguezThank you for going to all of that work to explain how to make that graph. I am at least started, because I have a google spread sheet with our mileages, etc. on it.

I appreciate your effort!
Reply to this comment
2 years ago

Keith AdamsTo Kelly IniguezMy pleasure. I used to do a lot of that sort of thing for a living, so it came easily enough.
Reply to this comment
2 years ago

Kelly Iniguezhttps://www.cycleblaze.com/journals/60/maps/

I hope that comes through. I've created my chart, but it shows up small/not readable. Yours is full size. What didn't I do? I took a screen shot, saved it to photos, and then posted it in my journal. But, it's too small.
Reply to this comment
2 years ago

Keith AdamsTo Kelly IniguezI used my regular computer, which has a 22" diagonal monitor that I run at pretty high resolution. I maximize the browser window and also put the chart on its own tab so it takes the whole screen.

When I copy the screen and paste it into Paint (I live in the Windows world) the original image is nearly the size or my monitor, so it still displays well when it gets scaled down as a CycleBlaze image.
Reply to this comment
2 years ago