write a program in word VBA that will use the method of least squares (linear re

Question

write a program in word VBA that will use the method of least squares (linear regression) to calculate the m, b and r values for a given set of data points. The given data represents information about the volume of water through a culvert (in ft3/s), as a function of the height of the water in the culvert (in inches).

Program requirements:  

-Use a main program and a Subroutine subprogram.

-In the main program, read from the given file (LeastSqrs.txt): on line 1 of the file, read a number which equals the number of data pairs, n (n will never be more than 100); then starting on line 2 of the file the data pairs are listed, (x, y), one per line. Read the data pairs into one-dimensional arrays (one array will contain the x values and the other the y values).

-In the main program, ask the user if they want you to calculate the regression equation for a linear, exponential or power situation.

-Based on the users’ response, calculate and send to the Subroutine the correct form of the x and y values (you may need Lny, etc.).

-Use only ONE Subroutine to calculate the m, b and r values using the method of least squares to do Linear Regression (see equations on back of sheet).

-In the main program, output to the screen the linear, exponential or power equation with the calculated values of m and b inserted in the equations. Also output the r value.

file content shown below

-On line one, read the value of n (the count of data pair in the file).

-Starting on line two, the data is given as X, Y

n=11

Explanation / Answer

Ans.

program:

Public Function FindLinearLeastSquaresFit(ByVal points As _

List(Of PointF), ByRef m As Double, ByRef b As Double) _

As Double

' Performs calclation

' Finds the values 'S1', 'Sx', 'Sy', 'Sxx', and 'Sxy'

' s1 points.count

Dim S1 As Double = points.Count

' Sx

Dim Sx As Double = 0

' Sy

Dim Sy As Double = 0

' Sxx

Dim Sxx As Double = 0

' Sxy

Dim Sxy As Double = 0

' PointF

For Each pt As PointF In points

' pt.X + Sx

Sx += pt.X

' pt.Y + Sy

Sy += pt.Y

' (pt.X * pt.Y) + Sxx

Sxx += pt.X * pt.X

' (pt.X * pt.Y) + Sxy

Sxy += pt.X * pt.Y

Next pt

' Solving for the m & b

' m calc

m = (Sxy * S1 - Sx * Sy) / (Sxx * S1 - Sx * Sx)

' b calc

b = (Sxy * Sx - Sy * Sxx) / (Sx * Sx - S1 * Sxx)

' return Math.sqrt

Return Math.Sqrt(ErrorSquared(points, m, b))

'end

End Function

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.