Using spreadsheets for Parametric analyses.

Getting Started

The most important thing you need to think about is what, exactly, you want to show your reader. You should be able to say it in one sentence, eg. “For a given load, beam length has an important effect on beam deflection.”

Setting up the Spreadsheet

The spreadsheet should be set up so that the parameters you want to show with the most detail are “inside” the calculation grid. The parameters you want to show with less detail are “outside” the calculation grid. An example below will explain this more.

Spreadsheet Basics

This exercise is intended to show you how to use four different features of Excel

1. How to write formulas for calculation

2. How to use an “if” statement

3. How to graph data as a CEE would normally do

4. How to set up a simple parametric study via spreadsheet

Formulas for Calculation

Excel uses cell addresses to perform calculations such as “take what is in B1 and add it to what is in C1 and put it in D1.” One would simply move to cell D1 and type in

=(B1+C1)

If there are numbers in cells B1 and C1 one would get the expected results in D1. Try it, then copy and paste this formula in cell D2. Note that the formula has changed to

=(B2+C2)

That’s because Excel uses relative addressing when referring to cells. This is convenient sometimes, not so convenient other times. Suppose we want to calculate stress by the formula

stress = load / area

for several different loads, but the same area. Our spreadsheet might look like this:

Figure 1. Simple spreadsheet computation

We always want to use the same area value at B2, but different load values (A5, A6…) to compute our stress. Excel allows us to use absolute addressing for this. An absolute address is preceeded with a $. Our formula might then look like this:

=(A5/$B$2)

Then we need only write the formula once, in cell B5, then copy/paste it into the rest of the cells. This is shown below:

Figure 2. Formulas pasted in

Note that the relative addressing follows the cell address down the column. The absolute address stays at $B$2. Lets put in another area value in C2 and copy/paste the formula down colmn C.

Figure 3. Attempt at Column C

Well, shazaam!…It didn’t work! The relative address for load followed us to column B when we really wanted it to stay at column A. The absolute address stayed at $B$2 when we wanted it to scoot over one column. This time we use a mixture of relative and absolute addresses. We always want to go back to column A, so we use $A, but we want relative locations for rows 5,6,7… so we use plain old 5. The address for load will then be $A5. By the same argument the address for the area will be B$2 (assuming we wrote our formula at location B5) Here is the new formula, slightly different, but that difference is very important.

Figure 4. Correct Formulation

Now copy/paste the new formula to the remaining cells and see what happens.

Planning your formula and the numbers used for inputs is the most important aspect of this exercise. If you master the ability to plan your formula, you have won the game.

Setting up a Chart

Inserting a chart in your spreadsheet is a very useful and important capability. Most Civil & Environmental applications will use an X-Y chart (not a line chart). For now we will connect data points with straight lines, not curves. Curves will help sometimes, but can be very misleading other times.

1. Go to Insert – Chart – XY (scatter) – connect with straight lines

2. The chart wizard will then show a sample plot, and ask about data ranges.

3. Click the series tab and look at the two data series you now have

Figure 5. Chart Wizard, Series Selection

4. For each series there is a Name, X-values and Y-values. Click inside the name box and type in the words Test One (delete the rest of the box stuff, Sheet1!….etc. from only that box).

5. Click the other series (it will highlight in blue) and change its name to Test Two.

Figure 6. Chart source data changes

6. Click Next and go to step 3. Add a Chart title, label the axes (use appropriate names). Click the Gridlines tab and add X-major gridlines.

7. Click Next and Save as a new sheet. Call the sheet Load vs. Stress. Click finish.

Figure 7. Finished Chart

Note that the chart is fairly self-explanatory. All the information you need is on the chart. You don’t need to go back to the spreadsheet to figure anything out. You may want to inform the reader what the area values were for each test by changing the legend, or adding text labels to the chart.

Beam Analysis Spreadsheet

There are many ways to organize your spreadsheet. I would suggest having most of the parameters (except two) somewhere up near the top. The two you want to examine will be on the left column and on the row directly above your formula results. Note that I put the position x, along the beam where I calculate deflection in column A, and the point of load application, a, along row 6 as shown below

Figure 8. Start of Beam Analysis

Note that I used a little formula to compute the proper value for E in psf and another little formula for computing I. Now comes programming the formula for deflection. Starting in cell B7 we can enter in the following formula which is almost totally correct. Recall our formula looked like this:

In our Excel formula we would have the following:

=(-$B$2/(6*$D$2*$F$3))*((($B$3-B$6)*($A7)/($B$3))*

($B$3^2-($B$3-B$6)^2-$A7^2)+(Something))

You might want to omit the negative sign at the beginning, its easier to view the graph later. Note that I made the mixed addresses in red to help you see where the x, and a, values are in the formula.

In order to fill the something part we have to use an IF statement. This means

IF some condition, like x > a is true then

compute a formula like ($A7-B$6)^3

or else

compute another formula.

In Excel the if statement comes from the function menu, logical functions. Your final IF statement will look like this

(IF ($A7>B$6,($A7-B$6)^3,0))

This statement does just what we want for our beam analysis, that is

IF x>a then

compute (x-a)³

else

0

You are now finished with the formula, and the first part of the spreadsheet.

For the second part, “freeze” the value for a, and vary something else. You should move a up to the top of the spreadsheet somewhere, and bring down the parameter you want to vary.