In regression analysis, Microsoft Excel calculates for each point the. Vba is ideal so that I can use the values for other purposes. There are multiple facets to the vba sub I am trying to write. The result is the anticipated date when you will reach the target weight.Įxcel calculates the "trend line" (using linear regression) of the points in A2:B11 (i.e., it assumes there is a linear relationship between the dates and the weights. The line- and curve-fitting functions LINEST and LOGEST can calculate the best. I have been trying to work out which worksheet or vba function to use to calculate the following - but I just can't get the right answer. If your target weight is 160 lbs., you could use the following formula: The intercept point is based on a best-fit regression line plotted through the known x-values and known y-values. Calculates the point at which a line will intersect the y-axis by using existing x-values and y-values.
You will be asked to estimate a simple linear regression for each of these datasets. You will see 4 datasets, each dataset has an X and a Y variable. After getting about 10 days or so of measurements, you can use these data pairs to forecast when you will hit your target weight. This article describes the formula syntax and usage of the INTERCEPT function in Microsoft Excel. Regression Linear regression Locate the HW 10 excel file on BB. Every day you enter the date into column A and the weight for that day in column B. In Excel, there are built-in features with which you can calculate the parameters of the linear regression model.
In our example as Y serves the indicator of employees who retired. The Yrange and Xrange parameters are sets of know Y and X values.Īs and example, let's say that you are going on a diet, and you decide to keep track of your weight each day. Where a are the regression coefficients, x the influencing variables, k the number of factors. In this usage, X is the X value for which you want FORCAST to return a Y value. The FORECAST function uses the following syntax: The new x-value can have any value and is not restricted to be larger than the existing x-values. Excel performs a linear regression based on the existing values and then inserts the x-values into the expression for the regression which gives you an y-value. The FORECAST function can be used whenever you have an existing set of data pairs (x-values and y-values) and you want to calculate an estimated y-value to a new x-value.