Spreadsheet Maniac

First, today was an easy day as I am doing a mini taper for the Bakersfield Half Marathon. Speaking of Bakersfield it looks like we are going to have a nice crowd from the AV heading that way. The Rubles are going. The Gutierrezes are going. Clay, Joe Kim, Jim Pandya, and Jesse are going. Justin Patananan and the Diorios are considering it as well.

Now Spreadsheets…. Last year I posted a Spreadsheet on the High Desert Runners web page called “Best of 2013.”  This page listed the top run at 1 mile, 5k, 10k, 10 mile, half marathon and full marathon for each age division. When I made this spreadsheet I managed to figure out how to get it to grab data from the spreadsheet where I record the races people submit throughout the year.  It grabbed the race data, sorted it and figured out who was the top in each division / race distance automatically -pretty cool.

At the end of the year, I was curious about the age graded times of these results AND the age graded times of other races that did not make the chart. I manually calculated these from a web page.  Now that it is 2014, of course I have started a new page to record peoples races. Then I made a “Best of 2014” spreadsheet. I really liked having the Age Graded percentages on the page so I began to wonder if there was a way to get my spreadsheet to automatically calculate them.

I found USATF Age Grade Calculator that had a link off to the side to a excel spreadsheet with all the data needed to make these calculations!  This is no simple problem. To calculate an age grade percentage you have to take the time in seconds of the current world record for the race distance. You divide that by an age factor provided in the table (ex: .9494) and divide that by your race time in seconds.

So, I added some calculations to the hidden pages of the spreadsheet. I figured out how to take any race time and convert it to seconds. Then it looks at the gender and race distance to find the appropriate world record time and the appropriate factor. (This was the trickiest part.)  Then it does the math and comes up with the percentage.

Now I new sheet that will have the times people have submitted for the year, sorted by Age Grade…. Guess who the current leader is?

I have added links to these pages to the right hand column of this blog. I will also have them placed on the HDR website..

One thought on “Spreadsheet Maniac

  1. I am very impressed with your spreadsheet writing capability. You would fit right in with all the Aerospace Engineers that I work with.

    The AV is going to be very well represented in Bakersfield. Good luck to everyone!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s