Jump to content
Sign in to follow this  

Can Any Excel Experts Help Me Write a Roll Chart Calculator

Recommended Posts

So I raced my first enduro this past weekend. I did really well, mostly because the guys on my line had a computer which helped me to zero 6 of 10 checks :applause:. The test sections were all me, though :smirk:

Anyway, following the roll chart was a bit difficult. It tells me the speed average and the distance, leaving me to make time calculations on the fly. Not so hard on the road with relatively low averages, but nearly impossible in the woods, especially with the really small print (I do race 40+, afterall). So I thought I would make my own roll chart and add a column for time. Of course, this doesn't really help at secret checks, but if I can stay on time at the other landmarks, I should be pretty close. Knowing the speed average and the distance should make it easy to calculate, but when using a time formatted cell, I can't figure out how much to add to increase the time by one second without accumulating errors to throw it off as time/mileage increases.

For example, if the speed average is 24mph, it's 150 seconds per mile. With a key time of 8:00:00, my watch should read 8:02:30 at the 1-mile mark, 8:05:00 at the 2-mile mark, and 9:20:29 at the 32.19 mile mark. But adding a number equal to my speed average in seconds per mile times the distance in miles (150 s/m x 2 miles = 300 seconds or 5 minutes) doesn't increase a time-formatted cell in 1-second increments (or one minute, hour or day increments). So I need to figure out what number increases the time by exactly 1 second and then use that number to convert my speed average so it works out, but that number always seems to have a decimal in it, causing the accumulated error. Or maybe I need to time-format my speed average figure :excuseme:, or is it something else?

Sure, I could just buy an enduro computer, but I kinda feel like that's cheating and I'd really like to at least try to do it my way. So if anyone can help me, I'd really appreciate it.

Share this post


Link to post
Share on other sites

EDIT: Nevermind

Edited by DuncR6

Share this post


Link to post
Share on other sites

EDIT: Nevermind

Edited by DuncR6

Share this post


Link to post
Share on other sites

I tried to upload the file here but it said I wasn't "permitted to upload this kind of file," so here is a Google Docs link:

https://docs.google....dit?usp=sharing

I included two macros to help you as well so if you get a message asking if Excel should enable content then click yes if you want to use the buttons. You don't need to enable though to use the file.

The buttons are:

- "Reset Sheet" = it will put all the formulas & titles back to the original ones if you accidentally screw something up. It only does it up to the first waypoint then you can fill down.

- "Delete Unused Rows" = if you add say 10,000 waypoints your file will become large and the scrollbar will get really tiny. By clicking this button excel will find the last cell that has been used and make that the new end of the sheet. It deletes all the useless rows and your scrollbar will look normal again.

Edited by DuncR6
  • Like 1

Share this post


Link to post
Share on other sites

I tried to upload the file here but it said I wasn't "permitted to upload this kind of file," so here is a Google Docs link:

https://docs.google....dit?usp=sharing

I included two macros to help you as well so if you get a message asking if Excel should enable content then click yes if you want to use the buttons. You don't need to enable though to use the file.

The buttons are:

- "Reset Sheet" = it will put all the formulas & titles back to the original ones if you accidentally screw something up. It only does it up to the first waypoint then you can fill down.

- "Delete Unused Rows" = if you add say 10,000 waypoints your file will become large and the scrollbar will get really tiny. By clicking this button excel will find the last cell that has been used and make that the new end of the sheet. It deletes all the useless rows and your scrollbar will look normal again.

Yes, exactly what I wanted. I added a column for waypoints and a row for speed changes, then highlighted the changes, gas stops and resets. Works perfect. Thanks so much :worthy:

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Reply with:

Sign in to follow this  

×