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.
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:
Results Output
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:
Conclusion
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.
Are you struggling to the find the right solutions to your engineering problems in Excel?
In Engineering with Excel, you'll learn Excel for advanced engineering calculations through a step-by-step system that helps engineers solve difficult problems quickly and accurately.