Instead you should use the GROWTH function as it is better suited for your data. Fitting a linear trend to an exponential data set results in an underestimation when you forecast the data. In such a situation you wouldn't want to use FORECAST because it works on linear data, and a 5% increase per month is not linear-it is exponential. Suppose, for example, that you have data that increases by 5% per month over time, and you want to forecast how that data is likely to progress in the future. The suitability of the FORECAST function to what you are trying to accomplish is directly tied the characteristics of the data you are working with. (This trend line is the same as you would get from plotting the data pairs and adding a trend line to the chart.) Since xdate, Excel is probably using the integer representation of dates (12/2/15 is 42340), so you just need to create your 'future' x values in that scheme (tomorrow is 42341) HOWEVER, using a linear regression. Unless your metabolism is abnormal, your weight loss won't follow a straight line it will likely decrease over time.)Į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. Yes, you could use the best-fit line from that equation to forecast signups in the future. (This is, admittedly, a very simplistic example. The result is the anticipated date when you will reach the target weight.
If your target weight is 160 lbs., you could use the following formula: After getting about 10 days or so of measurements, you can use these data pairs to forecast when you will hit your target weight. Every day you enter the date into column A and the weight for that day in column B. The Yrange and Xrange parameters are sets of know Y and X values.Īs an example, let's say that you are going on a diet, and you decide to keep track of your weight each day. In this usage, X is the X value for which you want FORECAST to return a Y value. The FORECAST function uses the following syntax: Picture 1- The linear extrapolation formula in Excel.
A (a, b) B (c, d) The linear extrapolation formula is: Y (x)b+ (x-a) (d-b)/ (c-a) You can enter the formula according to two points of your data values and extrapolate the target value. The new x-value can have any value and is not restricted to be larger than the existing x-values. To extrapolate data by formula, we need to use two points of the linear chart that we plotted before.
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 a 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.
0 kommentar(er)
