/

# Excel in ME - Part 3 Keith A. Woodbury Mechanical Engineering - PowerPoint Presentation

## Excel in ME - Part 3 Keith A. Woodbury Mechanical Engineering - PPT Presentation

Excel in ME Part 3 Keith A Woodbury Mechanical Engineering University of Alabama Excel Matrices There is a built in solver in M icrosoft E xcel that can produce almost all the necessary matrix calculations that ID: 761541

#### Embed:

Download Presentation The PPT/PDF document "Excel in ME - Part 3 Keith A. Woodbury..." is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.

Presentation Transcript

Excel in ME - Part 3 Keith A. Woodbury Mechanical Engineering University of Alabama

Excel Matrices 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 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 – Example Three individuals are connected by bungee cords. Using Newton's second law we get the following force balance and for each jumper (k 1 +k 2 )x 1 – k 2 x 2 = m 1 g -k 2 x 1 + (k 2 +k 3 )x 2 – k 3 x 3 = m 2 g -k 3 x 2 + k 3 x 3 = m 3 g 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 Now lets set up our system of equations in Excel by substituting our given variables into the initial equations

Excel Matrices – Example

Excel Matrices – Example Substituting our parameters we get the following matrix equation 150 -100 0 x 1 588.6 - 100 150 -50 x 2 = 686.7 0 - 50 50 x3 784.8

Excel Matrices – Example 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

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

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

Excel Iteration 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 - Example C lick 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 Hit the F9 key to continue the iteration

Visual Basic Macros 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 - 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:

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

Visual Basic Macros - Example 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’.

How to find Visual Basic (Found under Developer Tab)

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

Select ‘Insert’ and then ‘Module’

Visual Basic Macros - Example 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.5m 3 /kg. Also don’t forget to name your variables in Excel.

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

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)

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

Visual Basic Macros - Example 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 3m 3 tank

Visual Basic Macros 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