 |
|
|
Spread sheets are a great tool you can use to make engineering or other
calculations.
Generic Spread Sheets |
GENERIC SPREAD SHEETS:
This page is not meant to teach you spread sheets, only to introduce you to them. We will learn how to use them in more detail in class. Within most spread sheet programs, you can:
- Insert and organize data
- Insert pictures/diagrams
- Do simple and "steroid" engineering calculations
- Plot the results
- Use a built in optimization program (i.e. for your gear pump)
- Otherwise manipulate your data
The working area of a spread sheet file consists of cells which are designated by which row and column they fall into. For example there are 72 cells in the 12 rows and 6 columns in the figure below. Although only 72 cells are shown, I have made spread sheets containing over 10,000 cells.
Cells can contain text, equations, images, and other useful things.
The gray boxes are not cells, they are the headings for the rows (1 - 12) and columns (A -
F.)
THE MICROSOFT EXCEL GRAPHICAL USER INTERFACE (GUI)
Excel is a spread sheet program which is menu and icon based. The GUI is shown
below. Following are short explanations of some of the menu items we will use in
class.
FILE MENU
The file menu is used to handle and print files.
EDIT MENU
The edit menu is mainly used to edit the content of your spread sheet, for purposes of this class, you will find it most useful for using the copy/paste functions and the find function (will find text/numbers in large spread sheets.)
INSERT MENU
From the insert menu, you can choose to insert/add extra columns, rows, cells, and CHART/PLOTS!!! We will be using the plotting function in a few of your labs. The next section will discuss this, but first....
You can use this menu to insert functions/equations (you could type them by hand.) Typical functions/equations would include trigonmetric, averages, and sums of rows or columns. There are also logical (and/if/or) functions, statistical functions, and many other equatons.
PLOTTING DATA WITH EXCEL
Say you wanted to plot the equation y= x2.
To get the results (see graphic belwo,) I followed these steps:
- Click on cell A2 and type "x"
- Click on cell B2 and type "y"
- Click on A4 and enter "1"
- Click on A5 and enter "2"
- Continue adding numbers (as seen in graphic below) up to A8
- In Cell B4 type equation "=A4*A4" (you could also have typed A4^2)
- Copy cell B4 to B5 (B5 contents will now refrence cells A5, not A4)
- Continue to copy this cell through B8
Your results should look like this
To make a plot, highlight the cells you want to pick. Then click on
in the insert
menu.
Excel will prompt you for the type of chart you wish to make, we will be using mostly scatter plots (#4.) To choose this type, highlight XY (Scatter) in the left window, then highlight the chart type labeled #4 in the graphic.
With respect to why we are using the #4 chart type and not the others, consider the following about each chart type:
1. This type hard to see and communicate trends
2. This type may lead others to make conclusions about trends, slopes, or at points for which you have no real data
3. This type hard for some to see original data points
4. This type works well, you should be able to say why considering types 1 -3...
You can finish the rest of the process by following the on screen instructions.
SPREAD SHEET EXAMPLE
Spread sheets are very useful for playing what if games, iterative calculations, and
optimizing designs. You can "program" the spread sheet to calculate say
for instance, the approximate normal stress in a beam as seen below. FOLKS WITHOUT SOLID MECHANICS, DON'T WORRY ABOUT THE EQUATIONS USED TO GET
THIS, CONCENTRATE HOW THE SHEET IS USED!
The stress depends on the
INPUTS:
|
Moment and stress are the OUTPUTS, or calculated
values. The moment and stress are calculated at different positions along the beam. The OUTPUTS are then PLOTTED to make their relationship to the INPUTS easier to interpret. |
Now suppose you want to know what the stress and moment are in the same beam loaded with a different force.
All you have to do is change the value in cell C20. Say we change it from 5 to 2 pounds. The spread sheet automatically recalculates and replots all the output.
FINIS!