1) The document describes using exponential regression in Microsoft Excel to determine the relationship between plate thickness (W) and counts per second (C) from a Geiger counter.
2) It provides measured data for plate thickness and counts and shows using Excel to calculate the natural log of C and perform regression analysis to determine the slope (m) and intercept (ln(b)).
3) The regression analysis finds that the data closely fits an exponential curve of the form C = 5856e-0.0771W, accurately describing the decrease in counts as plate thickness increases.
1) The document describes using exponential regression in Microsoft Excel to determine the relationship between plate thickness (W) and counts per second (C) from a Geiger counter.
2) It provides measured data for plate thickness and counts and shows using Excel to calculate the natural log of C and perform regression analysis to determine the slope (m) and intercept (ln(b)).
3) The regression analysis finds that the data closely fits an exponential curve of the form C = 5856e-0.0771W, accurately describing the decrease in counts as plate thickness increases.
1) The document describes using exponential regression in Microsoft Excel to determine the relationship between plate thickness (W) and counts per second (C) from a Geiger counter.
2) It provides measured data for plate thickness and counts and shows using Excel to calculate the natural log of C and perform regression analysis to determine the slope (m) and intercept (ln(b)).
3) The regression analysis finds that the data closely fits an exponential curve of the form C = 5856e-0.0771W, accurately describing the decrease in counts as plate thickness increases.
1) The document describes using exponential regression in Microsoft Excel to determine the relationship between plate thickness (W) and counts per second (C) from a Geiger counter.
2) It provides measured data for plate thickness and counts and shows using Excel to calculate the natural log of C and perform regression analysis to determine the slope (m) and intercept (ln(b)).
3) The regression analysis finds that the data closely fits an exponential curve of the form C = 5856e-0.0771W, accurately describing the decrease in counts as plate thickness increases.
Method: This method uses Excel's tool for regression analysis which determines the slope and intercept of straight line data. mx An exponential equation has the general form: y = be Taking the natural log of both sides of the equation yields the equation: ln(y) = mx + ln(b) The equation above is the equation of a straight line with y on a log scale and x on a linear scale. Regression analysis performed on the x data and the ln(y) data will yield the slope, m, and the intercept, ln(b). Problem 3.18 - Plot C vs W and use regression to find an exponential formula of the form y = be Geiger Counter: Counts per second vs plate thickness Measured data from textbook: Plate Thickness Geiger Counter W (mm) 2.0 5.0 10.0 20.0 27.5 32.5
Extra column for regression:
C (counts per second)
5500 3700 2550 1300 715 470
SUMMARY OUTPUT Regression Statistics Multiple R 0.997902337 R Square 0.995809075 0.994761344 Adjusted R Square Standard Error 0.069589815 Observations 6
ln(C) 8.613 8.216 7.844 7.170 6.572 6.153
df
mW
(or C = be
Regression data for
straight line on graph: mW C = be 5018.4 3981.5 2707.2 1251.6 701.8 477.2
formula: =B$47*EXP(B$43*A19)
Perform exponential regression as follows:
1. Form a column for ln(y) data - see above 2. Select Tools - Data Analysis - Regression from the menu (see Note below) 3. Use the x values for the independent variable and the ln(y) values for the dependent variable. For the output range, specify the cell location for the upper left corner of the report. Note: If Data Analysis is not listed under the Tools menu, first select Tools - Add-ins - Analysis Tool Pack
ANOVA Regression Residual Total
mx
SS MS F ignificance F 1 4.60275 4.60275157 950.4432 6.6E-06 4 0.01937 0.00484274 5 4.62212
Coefficients andard Er t Stat P-value Lower 95%Upper 95%Lower 95.0% Upper 95.0% 8.675157247 0.04943 175.486447 6.33E-09 8.537904 8.812411 8.537904 8.812411 -0.07714813 0.0025 -30.8292587 6.6E-06 -0.084096 -0.0702 -0.084096 -0.0702
Intercept X Variable 1
ln(b) = so b = e^ln(b) or b = 5855.6
8.675157247
Note: From cell B30, R Square =
mx
so y = be
m= -0.0771
or
0.995809 Good fit!
-0.0771W
C = 5856e
mW
Form an xy graph using W = x-axis series, C = 1st series, and C = be data = 2nd series. Use a log scales for the y-axis and a linear scale for the x-axis. The data should fall in a straight line. Show points only for the 1st series and line only for the 2nd series.
