Hello guys,
I would like to have an expert advise on point plotting and if the following is possible in Excel.
I have values from year 1996 to 2021 which forms a curve and I have the end value for year
2030. I want to trace the points between 2021 and 2030 based on the pattern of past values
(i.e. values between 1996 and 2021).
It is possible? Please help me out guys, I am stuck with my thesis.Example.jpg
Short but useless answer -- Yes, this is possible.
Generally, before plotting points onto a chart, one needs to calculate those values in the spreadsheet. How are you planning to perform the regression and/or smoothing and extrapolation? Are you looking for built in functions like the various FORECAST.xxxx() functions (https://support.microsoft.com/en-us/...0-93e0308d5f6e )? Do you have a different algorithm in mind?
We are usually pretty good at programming algorithms into Excel, if you can help us understand the algorithm you want to use.
Originally Posted by shg
Hello Shorty,
thanks for your reply. Yes I did presume using excel linear forecast function to forecast the values, but
I want those values plotted not only based on the past values but by also taking into consideration the
end value.
Otherwise, the values from 2022 to 2029 would show a subtle change and there would be spike because of the
end value for year 2030 which would be a bigger value.
I also tried using trendline and adjusting it such that it intersects the end point i.e value at 2030 but then
it is very difficult to estimate exact values of the points on the curve manually.
Would you have any other better ideas for this problem?
I don't understand the algorithm you want to use. As I indicated, we can help with the programming if you can help us understand the algorithm.
Assuming the years are in column A and the values are in column B, I can use =FORECAST(A1,$B$1:$B$35,$A$1:$A$35) to get the values along the best fit line for the data -- including the last point.
If you are looking for a linear fit that is forced to through the point 2030, 1182, then we need to spend some time with the algebra to get the constrained fit through that data point.
If you are looking for something else, help us understand what you want.
Hello Shorty,
There no as such any algorithm that I have in my mind, that is what I am exactly seeking help for.
Exactly! I am looking for a linear or exponential curve fit between 2021 and 2030 that is forced through
the point 1182.
If you could please help with this, then it would be great. I am sooo stuck with my thesis for this calculation.
Thanks for your reply and help!
Before we get to the Excel programming part, we need to manipulate our regression equation so that we can force it through the specified point. Straight line, for example:
regression equation: y=mx+b
forced through desired point: 1182=m*2030+b
solve for b: b=1182-2030m
substitute back into original regression equation: y=mx+1182-2030m
rearrange: y-1182=m(x-2030)
if it helps to see the new regression equation, substitute v=y-1182 and u=x-2030: v=mu which is an equation for a straight line where b=0.
In Excel, we can get m (and b) from such an equation using the LINEST() function https://support.microsoft.com/en-us/...a-fa7abf772b6d Add columns to calculate u and v in your spreadsheet, then use the LINEST() function to get m (3rd argument is FALSE to force b to be 0).
For an exponential function ln(y)=mx+b, the procedure is similar.
Try that and see if that is what you are looking for.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks