This blog shows how MS Excel and Google Sheets can be used for engineering and other technical applications. Examples of their use will be given and discussed. They will be in the form of COM Add-ins, macros, templates, user functions, Google scripts, simple forms, online calculators and other.
Discussion ideas are welcome.
This is the same drop shock calculator given in the 4/28/2014 posting. However, via javascript an animation feature has been added. Simply click the button to view the drop. This should work in all browsers except for IE 8 and earlier.
If the calculator has not been run for a few minutes the animation may not execute. To do so simply refresh the page.
A limited number of time steps are used in the solution. For large drop heights, low damping and stiff springs the drop response will be multiple low damped bounces and the animation will likely stop during a mid bounce. For smaller drop heights, moderate to high damping (>10%) and soft springs (fn < 10-20 Hz) the drop response will be one to a few rebounds and the animation will likely stop at the static deflection of W/k.
This is the second in a series of shock response online calculators. The calculator below or here will determine the shock response to a dropped weight. The spring and damper are mass-less and, therefore, the results are valid for both the case of 1) the spring/damper attached to the bottom of the weight and dropped onto a hard surface and 2) the rigid weight dropped onto the spring/damper. This version is limited to one vertical degree of freedom.
The input includes the weight, drop height, spring constant, viscous damping value, gravitational constant and initial velocity if not zero.
The calculations include un-damped natural frequency of the weight on the spring, critical damping value, damping ratio, time to initial impact, maximum upward (-) direction acceleration, maximum downward (+) acceleration and maximum deflection after impact.
Several response cycles are charted. The charted response is limited to the pre-coded number of time steps which is dependent on the drop height and natural time period. A caution note is activated if the limit is exceeded. The results are still valid with the caution but the time response will have fewer than the normally charted number of response cycles.
The calculation does not require that the weight adheres to the spring/damper at impact and beyond and therefore can "bounce."
Any consistent set of dimensional units can be used that is consistent with Hz and seconds.
For no damping the the maximum displacement will match the theoretical value to within 1% where W/k=Δ2/2(h+Δ).
Future versions will address nonlinear springs, multiple degrees-of-freedom, non-viscous damper types and more.
Here is an online calculator for the theoretical shock response to a base input. It has pre-coded options for 1/2 sine, triangular, rectangular and saw-tooth pulses where peak amplitude in g's and time pulse in seconds are input. It also has an option for a user input spectrum with up to 20 time points. The suspended system includes the suspended weight on a linear spring (k) and viscous damper (c). A gravitational constant is also needed. Any consistent set of units with seconds and cycles per second (Hz)are required.
Output includes a chart of the input shock and shock response versus time. The un-damped natural frequency, critical damping ratio and peak response-to-peak input are also output.
Some baseline cases where the analytical solution is known.
1) if a rectangular shock pulse is applied to an undamped system with a natural period of 2 times the pulse time the response is 2 times the input shock level.
2) if a 1/2 sine shock pulse is applied to an un-damped system with a natural period of approximately 1.7 times the pulse time the response is approximately 1.7 times the input level.
This active web form can be used to calculate the first 2 modes of a general beam configuration. Up to 24 different beam segments can be used with each having different section and area properties including weight density. Concentrated weights can also be added. Boundaries include free, simple support and fixed slope. Both vertical and rotational springs can also be used.
Output includes the first 2 mode frequencies in Hz and the respective mode shapes. A gravitational constant must be defined. All dimensional units must be consistent with each other and Hz.
A uniform beam of length = 24 inches, E = 30,000,000 psi, I = .25 in4, A = .28167 in2, weight density of 0.3 lb/in3 gave the following results compared to theoretical values:
Cantilevered:
Model f1 = 179.8; f2 = 1124.4 versus theory of f1 = 179.9; f2 = 1127.5 Hz
Fixed End/Simple Supported End:
Model f1 = 789.0; f2 = 2556.9 versus theory of f1 = 789.0; f2 = 2556.7 Hz
Fixed End/Fixed End:
Model f1 = 1144.9; f2 = 3159.7 versus theory of f1 = 1144.9; f2 = 3155.8 Hz
Reference: Theory of Vibration with Applications, W. T. Thomson, 4th Edition.
Simply change or add values to any of the white cells and the frequencies are automatically updated.
The following screenshot is a more complex configuration and with all of the features used.
This is an expanded Beam Analysis version from an earlier posting. This version has up to 24 segments available each with its own section properties and elastic modulus. Available loads include concentrated vertical and moments. Vertical and rotational springs are also available. Boundary conditions include simple, slope and fixed. Each can be "0" or specific values can be used. Charts of results include displacement, moment, shear and top and bottom fiber stresses. Maximum and minimum stresses and deflections are listed.
The following embedded Excel form is live. Any "white" cell can be changed and the results are updated immediately.
Updated: 3/14/2014 at 12:22 EST. We found that some of the worksheet tabs had the entire sheet protection activated which made the entire sheet non-editable. An undated version with all the tabs editable is available from the same link given below. Apologies for any inconvenience.
The attached Excel spreadsheet is an extension of previously posted beam calculators on this blog and XL4Sim. This one has two significant improved differences:
Beam Designer Dashboard.
1. Rather than using the VBA module to do the beam calculations they are done directly via worksheet formulas. This enables "instant" updates as changes are made to the input cells. This makes scenario projects very fast. The cell formulas are on the same worksheet as the input cell sheet and, therefore, the sheet can be copied as many times as needed without loss of functionality. Multiple workbooks can also be made.
2. A VBA macro option is available on the worksheet that will "optimize" the beam configuration given a user list of available beam sections defined by area moment area of inertia and corresponding section moduli (top and bottom). The optimizer will find the combination of given beam sections optimized to the user defined maximum stress using the smallest sections. The ideal optimum is where all the beam segments are at the maximum allowable stress which will usually correspond to the lowest weight and many times the lowest cost. The algorithm will consider the sections given by the user and will not become trapped in a local minimum.
It takes ~ 1-2 minutes to complete if the largest number of beam segments (24) and user input sections (21) are used.
Up to 24 beam segments can be used with each having different section properties. For the optimizer the user can input up to 21 different sections to be considered. The only restrictions are: 1) they be input from smallest area moment of inertia to largest and 2) the largest section, if used for the entire beam, will result in the maximum stress <= the target stress. The program will provide caution messages for each condition. In some cases the program will find sections that can be smaller than the ones the user has provided. These will show up as being stressed less than the allowable. The worksheet charts update after each cycle so you can watch as the optimization proceeds.
Available boundary conditions include simple-support with a given displacement value, fixed slope with a given value, and fixed displacement/slope. Translational and rotational springs are also available. Loads include concentrated forces and moments. Moment, shear, stress and deflection charts are given. The calculations are dimension neutral but they must be consistent.
As an aside, the built-in Excel Solver optimizer can be used but it is not very practical for this type of application because it gets too easily trapped in local minimums and can not easily handle discrete changes to the variables. The algorithm used in this spreadsheet is a guided descent approach which takes advantage of the known properties of the domain.
The spreadsheet download has several examples of trial cases (one per worksheet) that can be used as examples. The VBA module and cell formulas in Range A150:DC700 are password protected to protect them from being broken or inadvertently overwritten. The VBA module has been 3rd party code signed (Thawte) to help with the Excel security settings. The VBA module is also date limited for 6 months. See this blog or www.xl4sim.com for future updates.
Here are two examples. The first, is a fix-ended beam with a center load but the section moduli of the bottom fiber is 1/2 the upper fiber. The second is a bit more complex with an end moment, two vertical loads, a vertical spring, a fixed end and mid-span simple support. Note for each the optimizer tries to find a combination where each beam segment is close to the allowable stress which in these cases is 24ksi. They won't quite reach the allowable stress because of the discrete sections.
Another potentially valuable feature is once the "optimum" is found the user can manually change any of the beam parameters and get an "instant" result for additional "what ifs".
The subject online axial Euler buckling solver can determine the Euler buckling load for an axial member with up to 5 segments each with its own elastic modulus and area moment of inertia. Boundary conditions of free, simple support, fixed slope and or elastic spring can be applied at the start and end of each segment. The output includes the buckling load, maximum compression stress using the smallest input area and a slenderness ratio using the full length and smallest EA value. The later two output parameters can be used to determine if Euler buckling is an adequate representation of the simulation. Compression stress values approaching the material yield and low slenderness ratios suggest nonlinear buckling maybe more appropriate.
The calculator matches the classic Euler buckling equation Pcr = EI(π/KL)2 for simple-simple (K=1), fixed-fixed (K=0.5), simple-fixed (K=0.7) and fixed-free (K=2) end conditions.
An extended version of this calculator which also includes a nonlinear option can be found at www.xl4sim.com.
The Euler Beam Calculator shown below is a web page (this page) embedded active Excel spreadsheet. It uses the Microsoft SkyDrive file sharing system and does not require the user to have Excel to use.
This calculator will solve a user defined multi-span beam. Up to 5 beam segments can be input each with its own elastic modulus, bending area moment-of-inertia, and top and bottom section moduli. Concentrated loads and moments can be input along with slope and displacement boundary conditions.
The solution provides a deflection chart, moment and shear diagrams and stress plots. The calculator updates immediately as cells are changed.
This calculator is active. Change a cell and watch it update the solution.
Rubber state of cure generally depends on the temperature time history during the mold cycle. It can be a complex thermal kinetics problem due to the transient nature of the cure and temperature in time and position. The attached MS Excel Macro is a temperature and state-of-cure calculator that has 5 molding configurations: 1) planar with one heated side; 2) planar with two heated sides; 3) cylindrical with a heated inner surface; 4) heated outer surface and 5) both inner and outer surfaces heated. For these configurations it is assumed that the molded shape has a high length-to-thickness ratio allowing the problem to be treated 2-dimensionally.
The dashboard looks like this with a worksheet tab for each configuration.
Rubber State-of-Cure Calculator Dashboard.
The calculator uses the user input cure curve information for the rubber and the calculator solves the time/position/temperatures and the resulting state-of-cure versus time and position.
This section was added on 2/22/2014:
The implicit finite difference formulation is used to solve the for the temperatures in time and position. The implicit method is unconditionally stable in time, although this does not preclude using a small time step for improved accuracy. Progressively decreasing the time step until convergence is always good practice. The same is true for the spatial size. In this case the number of nodes. Any number of good references are available that describe the methodology including Fundamentals of Heat and Mass Transfer by Incropera and DeWitt, 4th Edition.
This module assumes that the thermal properties (conductivity and specific heat) do not change over the temperature range and state-of-cure. It also assumes there is no significant additional heating from the curing kinetics.
The MS Excel Macro has been code signed with a 3rd party code signing certificate to allow higher macro security settings.
A version enabling a larger range of shapes is underway.
Many thermal management systems have unsteady heat loads. Heat sink design and/or selection can be considered with these unsteady loads in mind.
An example of a natural convection finned heat sink is used to show how the thermal mass of the heat sink can be used to reduce its required cooling capacity. Generally the effects of thermal mass are intuitive but calculating the benefits can sometimes be challenging. This example shows an easy and quick simulation for evaluating the potential benefits of the transient behavior.
XL4Sim Simulation of a Transient, Naturally Cooled Heat Sink.
A typical natural convection heat sink is used. It is 93.65 mm wide and 75 mm long with a base plate thickness of 6.35 mm, fin spacing of 10.14 mm, fin thickness of 1.5 mm and fin height of 24.5 mm - see the attached. Two heat sources are used, 9 and 18 W located and sized as shown. The heat load duty cycle is 5 minutes at full power followed by 10 minutes at 1/2 power. The heat sink is assumed to be oriented with the fin channels vertical in free air at 25 C.
The maximum predicted heat sink temperature is 87.7 C for a corresponding delta T = 62.7 C. The time/temperature plot is shown below for the maximum temperature location.
Time/Temperature Plot.
After ~ 4 full power cycles the temperature has peaked. This animation shows the heat sink temperatures over the heat cycles.
Animation of the HX Temperatures over Time.
If the full power heat load is conservatively used then the steady state maximum predicted temperature is 98.9 C or a delta T = 73.9 C. This is a ~ 18% increase over the transient solution.
Steady Temperatures Assuming Continuous Full Power.
If the cyclic heat load was predictably constant then the heat sink could conceivably be reduced in size for space and expense savings.
The above analysis was done using the Heat Sink module of the XL4Sim series. The full analysis was setup and completed in less than 2 hours demonstrating the ease of use and efficiency of the simulation tool.
Attached is an MS Excel Macro of a 2 degree-of-freedom vibration simulation model that calculates the response to a random vibration input - PSD (power spectral density).
2-DOF Random Vibration Dashboard.
Vibration isolation strategy for tonal systems is generally straight forward where one can isolate by using soft isolators that put the forcing frequencies above the isolation frequency. Or tune between forcing frequencies. Sway space, damping, isolator fatigue and other considerations are needed as well.
For random vibration input the strategy is not so straight forward. In theory, all frequencies between the limits of the PSD are present. Any system natural frequency within this range would be set into resonance. Sway space, damping and other considerations are still important.
Typical PSD Curve.
The simulation model of the macro uses 2 masses with the random vibration input at the base. It could simulate the frame of a car that has an electrical containment box attached to it that in turn has a circuit board mounted inside the box. The circuit board is depicted as body (mass) 2, the box depicted as body (mass) 1 and the car body the rigid body that randomly vibrates the box from road irregularities, drive-train, and other sources..
2 DOF Vibration Model.
The simulation model has the following features:
M1, M2, K01 and K12 are input by the user.
Modal damping ratio can be input.
A PSD curve can be input.
A user defined gravitational constant is input.
Any consistent set of dimensional units can be used.
The simulation can calculate:
PSD Grms value; the 2 natural frequencies and mode shapes.
Mode shapes can be animated.
RMS response G's and relative displacement values.
A pseudo time series can be determined from the PSD.
A dynamic transient analysis of the time series can be done and response values determined.
The transient response can be animated.
Random Vibration Response.
For a typical example the following was simulated: a 5 lb circuit board mounted inside a 50 lb box which is mounted to a car frame that has a uniform 5-500 Hz 0.5 G2/Hz random vibration input. Low modal damping of 2% was also assumed. Two scenarios were evaluated.
First, it was assumed the circuit board was on mounts and/or isolators that gave it a natural frequency of 25 Hz. The box was then mounted to the car frame by a progressively softer attachment. Starting virtually hard mounted and then softened to a a frequency near the 25 Hz. The results are shown below. The box acceleration response is initially high because it's primary mode (box-to-car frame mode) is within the 500 Hz PSD range. As its mounting gets softer that mode drops in frequency and the response drops. As that frequency gets closer to the circuit board mode frequency they start to dynamically couple and more response is felt by the circuit board. As the frequency ratio (2nd mode-to-1st mode) approaches 2 from the right side of the chart the circuit response starts to increase dramatically.
Body 2 at 25 Hz and Body 1 Mounted with Varying Stiffnesses.
The second scenario was to assume the box-to-car frame natural frequency is 25 Hz and the circuit board mounted progressively softer from starting with a "hard" mount case. The results are below.
The trends are similar to the first scenario except that the box response starts out relatively low because it's already at a low natural frequency and the circuit board is isolated by the box from the car. As the circuit board mounting becomes softer the acceleration response of both the box and circuit board increase. At a frequency ratio of 2 they are feeling large responses from their mode coupling. In this case the mode 2 is predominately the circuit board mode. Whereas in the first scenario the box was predominately mode 2.
The take ways from this simple study are: 1) For 2 DOF system keeping the 2 modes separated by a frequency ratio of at least 2 (an octave) or more would be desirable from an acceleration response prospective. A frequency ratio greater than 4 appears to offer diminishing returns. 2) Isolating the box is preferred over isolating the circuit board assuming no other considerations make this undesirable like available sway space for the box which increases as the isolation frequency drops.
The attached spreadsheet summarizes the details shown in the two preceding charts.
Of course, other factors need to be evaluated. For example, this simple case used a uniform PSD input. Most PSD's decrease at the higher frequencies. Additional damping can be used, especially if elastomer isolators are used.
Note: The MS Excel Macro has been code signed with an independent code signing certificate to allow higher security settings. The macro has a time limit of 1/1/2015. Updated versions will be accessible at www.XL4Sim.com.
This is a link to an Excel COM Add-in for a module that was written to do beam analyses. It will do statics, modal, steady response and transient response on beams with varying materials and section properties. Loads include concentrated forces and weights, linearly distributed and beam weight. Boundaries include free, simply-supported, fixed, deflection, slope, translational spring and rotational spring.
The Add-in can be used in any version of Excel from 97-2003 and later and will create the menu structure for the appropriate version. Examples cases are provided to show how to use and setup models.
Note:The Add-in can be turned off within Excel or deleted by the "removing programs" option in Windows.
This link is the Excel 2007 version of the Calculator. This is a macro and has been trusted source code signed with a third party (Thawte) certificate so it doesn't jam up your Excel security settings if you want to download it.
It includes SAE 429 UNC and UNF bolts and ISO Metric bolts with all the common tensile strength grades. The bolt type (in-lb or metric), thread type (fine or coarse), size and grade are selected by dropdown lists. The preload calculation uses a user input range of torque tolerance, thread friction range and bolt head friction range. The maximum and minimum preloads are determined and compared to the proof and tensile loads for the selected grade. For reference the "nut factor", K is also calculated and given.
This link is a Google sheets version of the SAE 429 Bolt Preload Calculator for the shared "view only" version. For an editable and shared version use this link. Any changes will automatically be added to the shared version. I would suggest making a copy of the original shared version and use and control that as you wish.
(Disclaimer: I've just starting using Google sheets and am still learning how the Google "My Drive" and sharing system works. This is my current understanding and seems to work with the testing I've done. The Google scripting I've used to do the sheet calculations and load the drop-downs on the active sheet works fine and should work for the editable version. By design the "view only" shared version of the sheet will be completely inactive.)
The Google sheets version currently only includes the SAE 429 UNC and UNF bolts with the common grades. As mentioned Google script is used to populate the drop down lists and run the calculations.
Multiple scenarios can be evaluated and saved by copying the workbook and/or worksheet. The macro and script get copied along with the worksheet which can be named anything. All normal spreadsheet functionality is maintained.
The attached spreadsheet is an example of using the Excel Solver function to estimate parameters for the nonlinear transient thermal model of a battery used on a hybrid vehicle.
The temperature data was collected on a rack of batteries that were repeatedly charged and discharged. The batteries were cooled by natural convection. The data used in the example analysis was collected over roughly a 24 hour period. The ambient temperature at the rack ranged from ~ 32.5 to 37.5 C.
The temperature trace used was from a thermocouple located on the case of one of the batteries.
The first pass model was a single lumped mass of an individual battery with natural convection cooling and joule heating. This was represented by:
McpdT/dt = Q - hA(T-Ta) where Mcp is the thermal mass, Q the heat load, hA the heat transfer coefficient times the surface area, and Ta the ambient temperature.
The heat load, Q, was defined by QfI2 = Qcharge I2 for the charge cycle and QdischargeI2. The electrical current is I and ranges from 240 amps for the charge and 300 amps for the discharge. Note: For this example it is assumed that any heating from chemical kinetics is secondary and captured by the Q terms.
The natural convection, hA, is defined by Ch(T-Ta)x(T-Ta). This is based on natural convection heat transfer coefficients being estimated by CRan where Ra is the Rayleigh number and the exponent n ranging from .25 to .35 depending on the surface type.
The overall equation looks like
McpdT/dt = QfI2 - Ch(T-Ta)1+x.
This can be setup in a finite difference form in time as
The spreadsheet is setup with this form of the model.
Solver can now be used to find the best fit values for Qf, Ch, cp, Ch and x. Solver will find local minima so several starting values should tried. The constraints can also be used if reasonable values are known. For example, the convective heat transfer coefficient, x, should be somewhere between . 2 and .35 based on textbook values for flat surfaces with natural convection. Reference: "Fundamentals of Heat and Mass Transfer" 4th Edition by Incropera and DeWitt pgs 492 - 501.
The values could be determined by using the full time period, however, more accuracy can be gained by breaking the regression into sections. For example, the cool-down section can be used with the Qf term set to 0.0 to find fits for Ch, x and cp. The heat-up section can then be used to find Qf charge and discharge using the previously determined Ch and x values. The cp value can either be used from the cool-down or see what value fits the heat-up.
The full model can then be checked with the best fit values from each of the sections. Some refinement is necessary to better fit the overall time period and this was done manually.
Each of the worksheets used for this approach is given in the attached workbook.
