University:
Kentucky State University
Course:
COS 232| Programming I
Academic year:

2021
Views:

24

Pages:

13
Author:

crawniadxfo

17.19 Here’s VBA code to implement linear regression: Option Explicit Sub Regres() Dim n As Integer Dim x(20) As Single, y(20) As Single, a1 As Single, a0 As Single Dim syx As Single, r2 As Single n = 7 x(1) = x(6) = y(1) = y(6) = 1: x(2) = 6: x(7) = 0.5: y(2) 6: y(7) = 2: x(3) = 3: x(4) = 4: x(5) = 5 7 = 2.5: y(3) = 2: y(4) = 4: y(5) = 3.5 5.5 Call Linreg(x(), y(), n, a1, a0, syx, r2) MsgBox "slope= " & a1 MsgBox "intercept= " & a0 MsgBox "standard error= " & syx MsgBox "coefficient of determination= " & r2 MsgBox "correlation coefficient= " & Sqr(r2) End Sub Sub Linreg(x, y, n, a1, a0, syx, r2) Dim Dim Dim Dim i As Integer sumx As Single, sumy As Single, sumxy As Single sumx2 As Single, st As Single, sr As Single xm As Single, ym As Single sumx = 0 sumy = 0 sumxy = 0 sumx2 = 0 st = 0 sr = 0 For i = 1 To n sumx = sumx + x(i) sumy = sumy + y(i) sumxy = sumxy + x(i) * y(i) sumx2 = sumx2 + x(i) ^ 2 Next i xm = sumx / n ym = sumy / n a1 = (n * sumxy - sumx * sumy) / (n * sumx2 - sumx * sumx) a0 = ym - a1 * xm For i = 1 To n st = st + (y(i) - ym) ^ 2 sr = sr + (y(i) - a1 * x(i) - a0) ^ 2 Next i syx = (sr / (n - 2)) ^ 0.5 r2 = (st - sr) / st End Sub 17.20 log N 0 1 2 3 4 5 6 log Stress 3.053463 3.024486 2.996949 2.903633 2.813581 2.749736 2.630428 n =7 xi yi = 58.514 ∑ ∑ xi 2 = 91 ∑ xi = 21 ∑ yi = 20.17228 x=3 y = 2.8817 ∑ xi yi − ∑ xi yi = 7(58 .514 ) − ( 21)(20.17228 ) = −0.07153 n ∑ xi 2 − (∑ xi ) 2 7 (91) − ( 21) 2 n a1 = ao = y − a1 x = 2.8817 − (−0.07153)(3) = 3.09629 Therefore, y = −0.07153 x + 3.0963 . Excel spreadsheet solution: least squares fit 3.2 3.1 log stress 3 Series1 Linear (Series1) 2.9 2.8 2.7 y = -0.0715x + 3.0963 R2 = 0.9617 2.6 0 1 2 3 4 log cycles 5 6 7 17.21 This problem was solved using an Excel spreadsheet and TrendLine. Linear regression gives 0.6 y = 0.0454x + 0.1077 2 R = 0.999 0.4 0.2 0 0 2 4 6 8 10 6 8 10 Forcing a zero intercept yields 0.6 y = 0.061x R2 = 0.8387 0.4 0.2 0 0 2 4 One alternative that would force a zero intercept is a power fit 0.6 y = 0.1827x0.4069 R2 = 0.9024 0.4 0.2 0 0 2 4 6 8 10 However, this seems to represent a poor compromise since it misses the linear trend in the data. An alternative approach would to assume that the physically-unrealistic non-zero intercept is an artifact of the measurement method. Therefore, if the linear slope is valid, we might try y = 0.0454x. 17.22 This problem was solved using an Excel spreadsheet. 0.5 0 0 0.5 1 1.5 2 2.5 3 -0.5 -1 -1.5 -2 -2.5 y = -3.7409x + 7.3503 R2 = 0.9908 -3 -3.5 l o g t e mp 17.23 Using Excel, plot a linear fit which results in R2 = 0.9949. Using an exponential fit results in R2 = 1, which implies a perfect fit. Therefore, use the exponential solution. The amount of bacteria after 30 days: y = 67 .382 e0.0257x y (30) = 145 .67 × 10 6 Amount of Bacteria Present over a Specified Number of Days 120 y = 2.2529x + 65.721 2 R = 0.9949 100 Amount 80 60 40 20 0 0 5 10 15 20 25 Days Amount of Bacteria Present over a Specified Number of Days 120 y = 67.382e0.0257x R2 = 1 100 Amount 80 60 40 20 0 0 5 10 15 Days 20 25

Chapter 17 VBA Code to Implement Linear Regression

Related Documents

Recommended Documents

Description

Related Documents

Want to Download This Document?

Free up your schedule!

Take 5 seconds to unlock