1 November 2000 Microsoft Excel Tutorial (version ‘97) The Use of …

Short Description
Microsoft Excel Tutorial (version ‘97). The Use of Log-Log and Semilog Paper. The following are data collected from an experiment: …

Website: www.che.uah.edu | Filesize: 33kb

Content
1
1 November 2000
Microsoft Excel Tutorial (version ‘97)
The Use of Log-Log and Semilog Paper
The following are data collected from an experiment:
X Y
0.05 0.61
0.37 1.65
2.72 4.48
12.18 9.49
20.09 12.18
33.11 15.64
54.6 20.08
90.02 25.79
What does a plot of X versus Y look like? Try it! Enter these data points into an Excel
Spreadsheet and Plot the data. (There is a separate tutorial that describes how to make
plots using Excel. Therefore, this topic will not be breached here.) Your plot should
look something like this:
This plot is certainly not described by a straight-line model fit (Y = m X + b, where m is
the slope and b the y-intercept). Perhaps the best-fit model what is referred to as a
“power law”. Let’s try such a model:
Y = bXm
If we take the logarithm (e.g., base 10) of both sides of the equation, and apply log
identities/properties, the following is derived:
0
5
10
15
20
25
30
0 10 20 30 40 50 60 70 80 90 100
X
Y2
Log Y = Log b + m Log X
Thus, a straight line fit should occur for a plot of Log X versus Log Y, with slope m and
y-intercept Log b. (b is then equal to 10 y-intercept.) Let’s try this. Using spreadsheet
formulae, the following should be obtained:
And here is the plot:
Indeed, this DOES appear to be modeled by a straight line. This can be confirmed via 2
methods. First, Trendline can be used (”Insert” menu, select “power” model for X versus
Y and “linear” model for LogX vs. LogY). Also, the Slope and Intercept built-in
functions can be employed (the topic of another Excel tutorial). These tools provide a
slope of 0.4998, a y-intercept of 0.4345, and a correlation coefficient of 1.000. The latter
indicates that the model fit is excellent. Thus, our data is modeled by the following
equation:
Log(Y) = 0.4345 + 0.4998 Log(X), or equivalently Y = 2.7916 X 0.4998 (where 10 0.4345 =
2.7916).
1
2
3
4
5
6
7
8
9
A B C D
X Y Log(X) Log(Y)
0.05 0.61 -1.301 -…