Today’s post is the final installment in the creation of a mass moment of inertia calculator in Excel.
In the first post I showed how to insert radio buttons to allow the spreadsheet user to select between various basic shapes.
Then, in the second post, I showed how to use the output from the radio button controls to conditionally format the input cells.
[Note: Want to learn even more about advanced Excel techniques? Watch my free training just for engineers. In the three-part video series I'll show you how to easily solve engineering challenges in Excel. Click here to get started.]
Today, in the final post, I’m going to add the formulas to calculate the moments of inertia and use VLOOKUP to report the results.
Moment of Inertia Calculations
Below the input dimensions I set up a table to calculate the moments of inertia for all of the shapes.
As you can see, I included the number corresponding to the radio button output in the first column. This way, I can use a lookup function to extract the calculated result from the table and report it clearly elsewhere in the sheet.
After entering all of the formulas and some values, the table looks as shown below:
When a user selects a shape, I want the displayed results to reflect that selection. I know what was selected based on the results of the radio button output cell, B16. I can use that output in a VLOOKUP function to dynamically update the result area based on the shape selected.
I copied that same formula for Iy and Iz, updating the col_index_num to reflect the position of the moment of inertia in the y- and z-axis in the moment of inertia table.
Finally, to make it absolutely clear what shape is being calculated for, I added a heading at the top of the results section that automatically updates with the radio button selection using VLOOKUP and the shorthand replacement for CONCATENATE, “&”.
The finished spreadsheet looks like this:
That concludes this 3-post series on the mass moment of inertia calculator. Hopefully these examples have shown how you can use form controls, conditional formatting, and lookup functions to make your spreadsheets function like standalone applications.