Microsoft Excel Spreadsheet



ENGR-1122: ENGINEERING COMPUTING

OKLAHOMA CHRISTIAN UNIVERSITY

Spring 2001

MS Excel( Spreadsheets

You should be starting to become quite comfortable with Microsoft( applications; they have many common features. The next application deals with spreadsheets. Microsoft( Excel 2000 is available to all students on the OC Campus Network. Excel( spreadsheets will be automatically saved with a .xls extension.

Introduction to Spreadsheets

“Spreadsheet” is the term used to describe a program file into which you program data, text, equations, and logical expressions into a matrix of grid locations called cells. Each cell has an address that consists of a letter (A,B,C,…,Z,AA,AB,…,IV) to indicate the column, and a number (1,2,3,…,65536) to indicate the row. Cell addresses used in other cells may use either relative addressing (i.e., D5) or absolute addressing (i.e., $D5, $D$5, or D$5). Excel( includes many built-in functions for financial, math, statistical, text, and logical expressions, calculations, and rules. Excel( also includes additional options for spell-checking, “wizards” for generating customized spreadsheets from a number of “standard” templates (i.e., leases, expense reports, amortization tables, budgets, time cards, etc.), graphing options, hyperlinks (to other locations in the spreadsheet, or other files, or websites, ...), and even saving your spreadsheet as an HTML file for web publishing. You can see the value of getting familiar with a spreadsheet program, as you will find that quite a few engineering problems lend themselves to spreadsheet solutions.

Getting to Know MSExcel(

By the end of these exercises, you will be able to:

• Retrieve an existing spreadsheet, edit and modify it per some particular formatting requirements, and spell-check the result

• Add additional sheets to your spreadsheet file, format each sheet appropriately for printing

• Enter tabular data, sum appropriate rows or columns, and graph the data points

• Write cell expressions using built-in statistical, logical, and math functions, such as for calculating your current course grade based on the criteria set forth in the syllabus

• Perform linear regression on data sets, generate empirical formulas for interpolating and extrapolating

• Graph spreadsheet data to show and compare trends and to demonstrate the validity of linear regressions

Part A

(complete all 3 parts; due 02/26/01)

1) Retrieve and Edit an Existing File

a) After invoking (starting) MSExcel(, open the file sample_a.xls from the ENGR-1122 subdirectory on Dr. Cassel’s faculty webpage.

b) Save the file to your \\student1 ENGR-1122 directory as “Last_First_HW1” (using your first and last name).

c) The first worksheet contains a weekly schedule for this term. Notice that the worksheet tab is labeled “Spring 2001 Schedule”. Modify the schedule to show your own classes and routine activities.

d) Make sure to maintain the text centering, border styles, and cell shading (as best you can ascertain) used by the instructor. Do not change fonts or font-sizes.

e) Correct the header text to replace the instructor’s name with your own.

f) Preview your spreadsheet to check its appearance.

g) Spell-check and save the file (do so often as you progress through this assignment).

1) Create Your Own Worksheet

a) Click on the tab marked “Sheet2”. This is an empty worksheet.

b) Double click on the tab and change the text to “OC Fall Enrollment History”.

c) Modify the page header and footer to mimic the first sheet’s (course name, title, your name, page numbers), with the exception of automatically inserting the new tab name in the top center title location (down one line from the other header text).

d) Recreate the OC Enrollment table your instructor will handout, with the exception that the shaded cells will include the appropriate sums for each: Total Engrg, College of S& E, and Total OC (shade those cells as shown). (You can copy the general format from sample_b.xls on the ENGR-1122 webpage). Use cell equations where possible, including the year cell, such that if you modify the starting year (1988), all subsequent years will automatically increment appropriately. You will find that on this spreadsheet, you need only write 4 unique cell equations that can be copied to the rest of the cells.

e) Size all the columns by auto-fitting the applicable data cells.

f) Use border styles as shown in the handout.

g) Center the table left-to-right by modifying the margins from the print-preview mode and place the top of the table 2 inches from the top of the page.

h) Insert an XY scatter chart to plot the 3 series of data on the same page as shown on the handout.

i) Include the appropriate Title, Legend, Axis labels, etc. on your chart.

j) Make the chart as large as feasible. Center it left-to-right.

k) See if you can get the data and chart to fit on one page.

l) Preview your spreadsheet to check its appearance.

m) Spell-check and save the file (do so often as you progress through this assignment).

2) Insert and Move a New Worksheet

a) Insert a new worksheet, and then move it to the end.

b) Change the tab name to “Course Grade Calculator”.

c) Modify the page header and footer as you did the second sheet.

d) Recreate the Grade Calculator table from the top of the second page your instructor will handout. (You can copy the general format from sample_b.xls on Dr. Cassel’s ENGR-1122 webpage). The shaded cells are the location for entering your own assignment scores and absences (do shade those cells only with the lightest gray shading). Use cell equations where possible, and be careful of cell formatting, including significant digits shown. You will find that you need only write about 9 unique cell equations that can be copied to the rest of the cells. Look in your syllabus for the course grading policy.

e) Add another nested “IF” such that the result in the appropriate columns only appears if that particular assignment has a non-blank score (as in second table). Your worksheet will have only 1 table!

f) Size all the columns by auto-fitting the applicable data cells.

g) First, test your spreadsheet with the sample data in the handout.

h) Finally, enter your own assignment scores and absences to date, leaving all other shaded cells blank.

i) Use border styles as shown in the handout.

j) Center the table left-to-right by modifying the margins from the print-preview mode and position the top of the table 2 inches from the top of the page.

k) Preview your spreadsheet (as if all 3 worksheets were printed at the same time) to check its appearance (especially headers and footers, and no “blank” pages).

l) Spell-check and save the file.

m) Submit this first half of your Excel( assignment (Last_First_HW1) to your instructor via email by 02/27/01.

The second half of the assignment will be introduced next Tuesday. Work ahead at your own risk. No questions regarding the second section will be answered before it is presented in class.

Part B

(complete all 3 parts; due 03/06/01)

1) Linear Regression of Discrete Data Points

Often one might need to determine the mathematical equation that describes an experimentally obtained data set. Data sets are either linear or nonlinear. A linear equation can be represented by a straight line of the form y = mx + b. Excel has the capability to analyze a set of data points and generate the linear equation which best fits the data, a process called linear regression.

PROBLEM

In the laboratory, a thermocouple was connected to a computer via an analog-to-digital converter to acquire temperature readings of a "hot pot" of tap water. The formula that governs the heating of the water is Q = mcp(T, where Q is the heat added, m is the mass of the water, cp is the specific heat constant, and T is the temperature. Since the specific heat and the mass are constant, adding heat at a constant rate (by the heating element in the hot pot), should result in a linear temperature increase with respect to time.

For the data set on the next page:

a) Enter the data into a new Excel spreadsheet file named “Last_First_Regress” (using your own first and last name. (see sample_b.xls on the ENGR-1122 webpage).

b) Enter a column that converts the Fahrenheit temperature to Celsius:

T (C = (T (F - 32)/1.8

c) Perform a data regression of the data using the LINEST function. Use time as the independent variable (x-axis) and temperature (in (C) as the dependent variable (y-axis). Be aware that you must first reserve an array of cells for the regression results, as the results include an array of values. Excel will display a number of statistical values that are not necessary to be understood at this point. The important values are:

i) b - the y-intercept of the line

ii) m - the slope of the line

iii) r2 -This is the correlation coefficient; a mathematical measure of how well the data fits the curve. A correlation coefficient > 0.9 indicates an acceptable fit. A correlation coefficient = 1 is a perfect fit.

d) Add a column labeled Test. Calculate Test as the best-fit value of temperature (in (C) for each time using m and b from the linear regression (i.e. Test = m (time) +b).

e) Show an appropriate number of significant digits.

f) Graph the experimental data with markers and estimate values with lines.

g) Include the appropriate Title, Legend, Axis labels, etc. on your chart.

h) Calculate the total time for the water to boil using the linear equation you obtained.

i) Include adequate typed explanatory notes and labels for the results and graph right on the worksheet. Include a typed comment concerning your observation about the fit of the data to the curve.

j) Make the chart as large as feasible. Center it left-to-right.

k) See if you can get the data and chart to fit on two pages (the chart on the second).

l) Add the same type header/footer. Preview your spreadsheet to check its appearance.

|Time(sec) |T((F) | |time(cont.) |T(cont.) |

|0 |67.16 | |155 |124.29 |

|5 |67.16 | |160 |124.29 |

|10 |67.16 | |165 |133.07 |

|15 |71.55 | |170 |128.68 |

|20 |71.55 | |175 |133.07 |

|25 |75.95 | |180 |137.47 |

|30 |71.55 | |185 |137.47 |

|35 |71.55 | |190 |141.86 |

|40 |71.55 | |195 |141.86 |

|45 |80.34 | |200 |141.86 |

|50 |80.34 | |205 |146.26 |

|55 |80.34 | |210 |141.86 |

|60 |84.73 | |215 |150.65 |

|65 |89.13 | |220 |150.65 |

|70 |89.13 | |225 |155.05 |

|75 |89.13 | |230 |155.05 |

|80 |89.13 | |235 |159.44 |

|85 |97.92 | |240 |159.44 |

|90 |97.92 | |245 |163.84 |

|95 |97.92 | |250 |163.84 |

|100 |102.31 | |255 |159.44 |

|105 |102.31 | |260 |168.23 |

|110 |106.71 | |265 |168.23 |

|115 |106.71 | |270 |177.02 |

|120 |111.10 | |275 |177.02 |

|125 |106.71 | |280 |181.41 |

|130 |115.50 | |285 |177.02 |

|135 |119.89 | |290 |177.02 |

|140 |119.89 | |295 |177.02 |

|145 |115.50 | |300 |185.81 |

|150 |124.29 | | | |

2) Advanced Linear Regression

In the previous exercise we used a curve of the form y = mx + b. In this exercise we will fit curves to data sets that are not linear, but by manipulation can be made to look linear. An example is Y = m (sin x) + b. Notice that y is not linear with x but it is linear with sin x. If we do a linear regression on y versus sin x, the output will be the value of m and b. Knowing m and b allows us to write a cell formula and compute and plot m (sin x)+b for any value of x.

PROBLEM

The thermal conductivity (() of silicon is measured as a function of temperature (T). Thermal conductivity is a material property that represents how well heat is conducted through the material. The following data set was collected:

T (K) k (W/cm-K)

250 0.455

300 0.362

350 0.302

400 0.256

500 0.223

550 0.197

600 0.158

650 0.144

700 0.132

750 0.121

800 0.112

850 0.103

a) Add a second worksheet to your “Last_First_Regress” file, labeling the tab “Advanced Regression.”

b) Enter the data and perform a linear regression (x range is T, y range is k). (See sample_b.xls on the ENGR-1122 webpage). Create a column with values of k est1 calculated using the m and b values computed from the regression. Graph the experimental data set using markers and the regression values as a line.

c) The thermal conductivity of silicon should vary according to the formula:

k = k 0/ (T-T0)

where k 0 and T0 are constants. Thus, k is not linear with T, but varies inversely with T. Rewrite the formula of the “form” y = mx + b where temperature is a function of thermal conductivity k to a form with k as a function of temperature.

d) Perform a second regression, this time on T versus k such that the values of k 0 and T0 can be found. Add an additional column with k est2 calculated from this regression. Add the results of the regression to your chart with a different type line than for k est1.

e) Show an appropriate number of significant digits.

f) Include the appropriate Title, Legend, Axis labels, etc. on your chart.

g) Make the chart as large as feasible. Center it left-to-right. See if you can get the data and chart to fit on one page.

h) Include a typed comment concerning the appropriateness of assuming linearity for the data set.

i) Add the same type header/footer. Preview your spreadsheet to check its appearance.

3) Polynomial Curve Fitting

a) Add a third worksheet to your “Last,_First_Regress” file, naming it “Polynomial Regression.”

b) Enter the data set on this page into the spreadsheet. (see sample_b.xls on the ENGR-1122 webpage).

c) Fit a linear equation to the data (Yest1=a1x + b1).

d) Fit a second order equation to the data (Yest2=a2x2 + b2x +c2).

e) Fit a third order equation to the data (Yest3=a3x3 + b3x2 +c3x + d3).

f) Fit a fourth order equation to the data (Yest4=a4x4 + b4x3 +c4x2 + d4x + e4).

g) Show an appropriate number of significant digits.

h) Graph the original data as markers, and the regressions as curves, each with different line styles (not just different colors) with no markers.

i) Include the appropriate Title, Legend, Axis labels, etc. on your chart.

j) Make the chart as large as feasible. Center it left-to-right. See if you can get the data and chart to fit on one page.

k) Which regression yields a good fit? How do you know?

l) Add the same type of headers and footers as the rest of the assignment.

m) Preview your spreadsheet (as if all 3 worksheets were printed at the same time) to check its appearance (especially headers and footers, and no “blank” pages).

n) Spell-check and save the file (do so often as you progress through this assignment).

o) Submit this second half of your Excel( assignment (Regress) to your instructor via email by 03/06/01.

p) Congratulations! You have completed the entire Excel( assignment!

_X_ _Y_

-1.5 18.50

-1.35 13.12

-1.2 9.57

-1.05 7.45

-0.9 6.39

-0.75 6.08

-0.60 6.24

-0.45 6.66

-0.30 7.16

-0.15 7.63

0.0 8.00

.15 8.23

.30 8.36

.45 8.46

.60 8.64

.75 9.08

.90 9.99

1.05 11.65

1.20 14.37

1.35 18.52

1.50 24.50

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download