VBA EXCEL Linear Regression
Linear Regression
Regression analysis fits an assumed function to a given set of data points. The function won't pass through all of the data
points, and it may not pass through any, but it will be the best fit of the assumed function. The best fit is found by
optimizing the function coefficients in order to minimize the error between the data points and the function, using the
method of least squares:
Minimize the sum of the square of the errors
Linear Regression fits a straight line to the data. That is, it fits the function:
𝑓(𝑥) = 𝑎0 + 𝑎1𝑥
Where 𝑎0 and 𝑎1 are the linear fit coefficients to be determined using the method of least squares. In the case of Linear Regression, the method of least squares results in explicit formulas for the coefficients, given by:
The Greek symbol ∑ (summa) means to sum the expression immediately following, in this case at each value of 𝑖. For example:
∑𝑥𝑖 2 = 𝑥1
2 + 𝑥2 2 + 𝑥3
2 + ⋯+ 𝑥𝑛 2
𝑛
𝑖=1
Reference the data in attached workbook, on sheet "Linear Regression", which includes a series of 𝑓(𝑥) vs. 𝑥 data
points; 𝑖 = 1,2,3,…𝑛, where 𝑥 is the independent variable and 𝑛 is the number of data points in the series.
Create a Main Sub procedure that will:
Read the data series from the "Raw Data" table into a two-dimensional array that is appropriately sized to store the
data.
Use loop structures to calculate the linear fit coefficients, and store their values in local procedure variables.
Write the linear fit coefficients to the output cells for 𝑎0 and 𝑎1.
Use a loop structure to read each "Total Time" value in the "Linear Fit" table, and write out the corresponding
"Velocity" value.
Add a printable run button that will execute your Main Sub.
Then, create a proper graph to display the raw data (using plot symbols only) and the linear fit (using lines only).
2
11
2
111 1
2
11
2
111
2
1 0
)(
)(
n
i
i
n
i
i
n
i
i
n
i
i
n
i
ii
n
i
i
n
i
i
n
i
ii
n
i
i
n
i
i
n
i
i
xxn
yxyxn
a
xxn
yxxxy
a