EXCEL IN ME PART 3 Keith A Woodbury

EXCEL IN ME - PART 3 Keith A. Woodbury Mechanical Engineering University of Alabama

EXCEL MATRICES � 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 – 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 x 3 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

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 Excel’s iteration and circular reference works

EXCEL ITERATION - EXAMPLE � 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 ‘F 9’ button.

EXCEL ITERATION Hit the F 9 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: pc = 4600 k. Pa Tc = 191 K R = 0. 518 k. J/(kg-K) You are asked to determine the amount of methane fuel that can be held in a 3 m 3 tank at a temperature of -40°C with a pressure of 65000 k. Pa.

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. 5 m 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 k. Pa Once we find our specific volume at 65000 k. Pa we can find the amount of methane fuel (kg) inside the 3 m 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