Excel in ME - Part 3

1. Keith A. WoodburyMechanical EngineeringUniversity of Alabama Excel in ME - Part 3

2. There is a built in solver in Microsoft Excel that can produce almost all the necessary matrix calculations that Matlab is capable of. • transpose • mmult • minverse • Naming matrices are different than naming cells/columns because the naming must be done by highlighting the entire matrix and going to the Formulas Tab. Select ‘Define Name’ and enter the name of the matrix. This can be seen on the next slide. Excel Matrices

3. Three individuals are connected by bungee cords. Using Newton's second law we get the following force balance and for each jumper (k1+k2)x1 – k2x2 = m1g -k2x1 + (k2+k3)x2 – k3x3 = m2g -k3x2 + k3x3 = m3g • The table of properties for each jumper is as follows Jumper Mass(kg) Spring Constant (N/m) Top (1) 60 50 Middle (2) 70 100 Bottom (3) 80 50 Excel Matrices – Example

4. Now lets set up our system of equations in Excel by substituting our given variables into the initial equations Excel Matrices – Example

6. Excel Matrices – Example

7. Substituting our parameters we get the following matrix equation 150 -100 0 x1 588.6 -100 150 -50 x2=686.7 0 - 50 50 x3 784.8 Excel Matrices – Example

10. In order to solve for our lengths (x) we will need to divide our spring constant by our forces: • x = k/mg • Unfortunately there is no matrix division in Excel therefore we must multiply the inverse of k by mg • x= k-1*mg Excel Matrices – Example

11. Make sure to hold down Ctrl+Shift before pressing enter in order for the matrix solution to work

12. Make sure to hold down Ctrl+Shift before pressing enter in order for the matrix solution to work

14. Microsoft Excel iteration can perform a “for-next” loop with no programming at all. In the next example we’ll set up a simple counter that will show how Excel’s iteration and circular reference works Excel Iteration

16. Click on the Microsoft Office Button (Circle Button in Top Left) and select ‘Excel Options’. Once that is done go to ‘Formulas’ on the left column. Make sure ‘Manual’ is selected under ‘Workbook Calculation’. After that check the box that reads ‘Enable iterative calculation’ and set your max number of iterations to 1. Then select ‘OK’. This will return you back to the spreadsheet. You can then continue the iteration by pressing the ‘F9’ button. Excel Iteration - Example

18. Excel Iteration Hit the F9 key to continue the iteration

19. Sometimes it is easier to place a formula inside a visual basic macro to solve a problem Using the macro makes it easier to identify the formula instead of having to copy it numerous times Visual Basic Macros

20. Visual Basic Macros - Example • The Redlich-Kwong Equation of state is given by: where R is the universal gas constant, T is absolute temperature, p is absolute pressure, v is the volume of a kg of gas, and a & b are constants with the parameters:

21. The parameters for the equation are: pc = 4600 kPa Tc = 191K R = 0.518 kJ/(kg-K) • You are asked to determine the amount of methane fuel that can be held in a 3m3 tank at a temperature of -40°C with a pressure of 65000 kPa. Visual Basic Macros - Example

22. The first step in the procedure is to set up a macro using Microsoft Visual Basic. You may need to add the Developer Tab on the ‘ribbon’ in Excel 2007. You can do this by going to the Microsoft Office Button (Circle Button in Top Left) and select ‘Excel Options’. Under ‘Popular’ on left column, check ‘Show Developer tab in the Ribbon’ under ‘Top options for working with Excel’. Then select ‘OK’. Visual Basic Macros - Example

24. How to find Visual Basic (Found under Developer Tab)

25. Once you select ‘Visual Basic’ from the Developer Tab, a new window will ‘pop up’. Then select ‘Insert’  ‘Module’. Visual Basic Macros - Example

26. Select ‘Insert’ and then ‘Module’

28. Now that we have our module let’s write our function into visual basic • Once we have our function set up in visual basic we can set up our parameters to solve for ‘p’. • Since we don’t know ‘v’ at this point lets make up a value of 0.5m3/kg. • Also don’t forget to name your variables in Excel. Visual Basic Macros - Example

29. Make sure that the module is in the modules folder and not the workbook folder

30. Go here to return to Microsoft Excel (Note: Your macro does not need to be saved individually. As long as the module is shown in visual basic the macro will apply to your spreadsheet)

31. Make sure you do NOT name this cell ‘p’ as it will interfere with the name of the macro

34. Now that we have our function set up we can use GOALSEEK to find our specific volume by setting our pressure equal to 65000 kPa Once we find our specific volume at 65000kPa we can find the amount of methane fuel (kg) inside the 3m3 tank Visual Basic Macros - Example

40. Note: Unlike Matlab all given variables in Microsoft Excel must be written in the actual spreadsheet instead of the function. Doing otherwise will not give you a result for a function Visual Basic Macros