1 / 40

Excel in ME - Part 3

Keith A. Woodbury Mechanical Engineering University of Alabama. Excel in ME - Part 3. There is a built in solver in M icrosoft E xcel that can produce almost all the necessary matrix calculations that M atlab is capable of. transpose mmult minverse

ellery
Télécharger la présentation

Excel in ME - Part 3

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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

  5. Excel Matrices – Example

  6. 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

  7. 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

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

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

  10. 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

  11. 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

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

  13. 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

  14. 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:

  15. 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

  16. 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

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

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

  19. Select ‘Insert’ and then ‘Module’

  20. 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

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

  22. 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)

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

  24. 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

  25. 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

More Related