Monday, September 17, 2012

Unique Excel Uses: Designing Roller Coasters

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.

roller coaster physics formulas
Formatting, constant values, a table containing statistics, and an embedded equation using Excel's native trig functions.

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.

unique excel uses
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.

matlab roller coaster
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.

Sunday, September 9, 2012

Mileage Reimbursement Form Template

Today I am going to share with you an Excel spreadsheet template I use every week, my mileage reimbursement form. Many companies today use mileage forms to help log and keep track of employee’s mileage when they travel. This template allows you to input your mileage as your total number of miles driven or you can enter your beginning and ending  odometer readings thus giving you your total reimbursable mileage. If you’re setting this up for your own business you can also choose different payback rates depending on if the vehicle driven is personal or company owned.

keep track of your miles form

My mileage sheet is very simple and easy to use. All grey colored cells are formulas. You shouldn’t need to change any of these and can use it as is. The mileage form assumes the report date is on Friday. The formulas then use this date to input the dates for the rest of the week using simple subtraction. I also use the ROUND function in order to round the amount owed to the employee to a nice number (like 9.85 instead of 9.84586).

Below is a link to a free download of my Mileage Reimbursement Form spreadsheet. Please feel free to let me know if you have any questions. Check out our Downloads page for more free Excel spreadsheets or the project management page for other templates and resources.

What about you? Do you have your own mileage log spreadsheet? Does your company track employee's mileage?

Effective Mileage Tracker