We’re
always looking for new and unique uses for Excel and I recently
stumbled upon a very thrilling example of what Microsoft Excel is used
for. Travis Rothbloom is a mechanical engineer and aspiring roller
coaster designer. He decided to design a roller coaster for a school
project using a combination of Excel and MatLAB. This massive
spreadsheet with roller coaster physics formulas contains 8500 rows by
50 columns of data! Travis explains how he compiled his engineering
spreadsheet:
The first thing that I established in my spreadsheet were the constant values that I used, namely gravitational acceleration, friction coefficients, and finite step size along with other parameters that helped define the physical geometry of the track. Excel's functionality of maintaining a reference's cell index with the "$" symbol really came in useful when I needed to change friction values - all I had to do was change the one cell storing the coefficient and the entire spreadsheet (thousands of lines of data) would update automatically.
Then
it was time to take the physics equations that I derived and embed them
into the spreadsheet. This was pretty easy given that Excel has
built-in methods for calculating trigonometric functions, powers/roots,
and division remainders while maintaining the proper order of
operations. Given that many of the calculated rows' values are dependent
on their respective column's previous value, I had to set up a row to
store initial conditions as to not cause a null reference. When I did
have null references or circular dependencies, however, it was easy to
spot the source with Excel's error handling mechanisms. Formulas that
described the dictating curves of the track, whether they be in
g-forces, roll angle, curvature radii, etc., relied on an incrementing
time index whose interval was dictated by the finite step size
parameter's cell.
With all this, I relied on Excel's formatting to help visualize what was transpiring in my spreadsheet. I highlighted both rows and individual cells to indicate what was a dictating, inputted value vs. what was being calculated by other values; this was not the same for every row because I would sometimes rearrange the equations for nuanced track elements, and using this color coded system made this a whole lot easier to keep track of. I also used blank columns highlighted with a color to separate columns into groups for easier viewing. Along with that, I frequently would hide multiple columns or rows to help navigate the spreadsheet as thousands of lines and up to 50+ columns of data can become unwieldy at times. Lastly, I created a table at the top of the sheet that maintained the maximum or minimum values of particular values such as speed and different g-forces.
Although Excel doesn't have a built-in 3D plotter, I created 2D plots of the track coordinates so I could view the track geometry within the spreadsheet. I stored these in separate tabs for easy navigation. Also stored in a separate tab was any other miscellaneous information that I would reference.
Finally, I made use of the fact that other programs usually have an easy time reading/parsing Excel documents. I frequently imported my spreadsheet into Matlab for further post-processing, including 3D plotting and some other calculations. It's not necessarily the case that Excel wasn't able to do any of these other things (for example, I have found user-created 3D plotting macros online) but rather I'm more comfortable coding in Matlab rather than VBA. People would often ask me why I didn't just work in Matlab for the entire project, to which my response was that I thought (and still do think) that working with mass data sets in spreadsheet format is best done by Excel and since it is found on so many computers and it's so easily read by other programs, it was easy to work on the project wherever I was.
Thanks again to Travis for sharing his awesome “Excel uses” example and good luck towards your goal of becoming a coaster creator - and let us know when you do so we can go ride your breathtaking creation! Read more details about Project Soar at his website.
Subscribe to our newsletter for more updates and drop us a line (or a comment) if you know of someone with a unique or interesting “Excel Used For” example.
The first thing that I established in my spreadsheet were the constant values that I used, namely gravitational acceleration, friction coefficients, and finite step size along with other parameters that helped define the physical geometry of the track. Excel's functionality of maintaining a reference's cell index with the "$" symbol really came in useful when I needed to change friction values - all I had to do was change the one cell storing the coefficient and the entire spreadsheet (thousands of lines of data) would update automatically.
 |
| Formatting, constant values, a table containing statistics, and an embedded equation using Excel's native trig functions. |
With all this, I relied on Excel's formatting to help visualize what was transpiring in my spreadsheet. I highlighted both rows and individual cells to indicate what was a dictating, inputted value vs. what was being calculated by other values; this was not the same for every row because I would sometimes rearrange the equations for nuanced track elements, and using this color coded system made this a whole lot easier to keep track of. I also used blank columns highlighted with a color to separate columns into groups for easier viewing. Along with that, I frequently would hide multiple columns or rows to help navigate the spreadsheet as thousands of lines and up to 50+ columns of data can become unwieldy at times. Lastly, I created a table at the top of the sheet that maintained the maximum or minimum values of particular values such as speed and different g-forces.
 |
| 2D plot showing an elevation of the ride |
Although Excel doesn't have a built-in 3D plotter, I created 2D plots of the track coordinates so I could view the track geometry within the spreadsheet. I stored these in separate tabs for easy navigation. Also stored in a separate tab was any other miscellaneous information that I would reference.
Finally, I made use of the fact that other programs usually have an easy time reading/parsing Excel documents. I frequently imported my spreadsheet into Matlab for further post-processing, including 3D plotting and some other calculations. It's not necessarily the case that Excel wasn't able to do any of these other things (for example, I have found user-created 3D plotting macros online) but rather I'm more comfortable coding in Matlab rather than VBA. People would often ask me why I didn't just work in Matlab for the entire project, to which my response was that I thought (and still do think) that working with mass data sets in spreadsheet format is best done by Excel and since it is found on so many computers and it's so easily read by other programs, it was easy to work on the project wherever I was.
 |
| 2D plot showing the ride's plan |
Thanks again to Travis for sharing his awesome “Excel uses” example and good luck towards your goal of becoming a coaster creator - and let us know when you do so we can go ride your breathtaking creation! Read more details about Project Soar at his website.
Subscribe to our newsletter for more updates and drop us a line (or a comment) if you know of someone with a unique or interesting “Excel Used For” example.
