Engineering Fundamentals: An Introduction to Engineering, 4th ed.c

14.8 Curve Fitting with Excel

Curve fitting deals with finding an equation that best fits a set of data. There are a number

of techniques that you can use to determine these functions. You will learn about them in

your numerical methods and other future engineering classes. The purpose of this section is

to demonstrate how to use Excel to find an equation that best fits a set of data which you

have plotted. We will demonstrate the curve-fitting capabilities of Excel using the following examples.

Example 14.10 In Chapter 10, we discussed linear springs. We will revisit Example 10.1 to show how you can use

Excel to obtain an equation that best fits a set of force – deflection data for a linear spring. For a given

spring, in order to determine the value of the spring constant, we attached dead weights to one end

of the spring, as shown in Figure 14.12. We have measured and recorded the deflection caused by

the corresponding weights, as given in Table 14.8. What is the value of the spring constant?

Recall, the spring constantk is determined by calculating the slope of a force –

deflection line (i.e., slope change in force /change in deflection). We have used Excel to

plot the deflection – load results of the experiment using the XY (Scatter) without the data points

connected, as shown in Figure 14.13. If you were to connect the experimental force –

deflection points, you would not obtain a straight line that goes through each experimental

point. In this case, you will try to come up with the best fit to the data points. There are math-

ematical procedures (including least squares techniques) that allow you to find the best fit to a

set of data points; Excel makes use of such techniques.

To add the trendline or the best fit, with the mouse pointer over a data point, click the right

button and choose Add Trendline... , as shown in Figure 14.14. Next, from the Format

Trendlinedialog box, under Trend /Regressiontype, select Linear, and toggle on the Set

interceptand the Display equation on chart, as shown in Figure 14.15.

448 Chapter 14 Electronic Spreadsheets

■Figure 14.12

The spring setup for

Example 14.10.

TABLE 14.8 The Results of the

Experiment for

Example 14.10

The Deflection

of the Spring

Weight (N) (mm)

5.0 9

10 17

15.0 29

20.0 35

Load (N)

25

20

15

10

5

0

0 1020305 15253540

Deflection (mm)

■Figure 14.13

The XY (Scatter) plot of experimental data points without the

data points connected.

Copyright 2010 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s).

圀圀圀⸀夀䄀娀䐀䄀一倀刀䔀匀匀⸀䌀伀䴀圀圀圀⸀夀䄀娀䐀䄀一倀刀䔀匀匀⸀䌀伀䴀