|
forecasting?
Hello,
Does Excel have a function that could help me predict or forecast, if you like, what week 7's number in below example could be? week1 11 week2 21 week3 35 week4 33 week5 39 week6 40 week7 ? Thanks, Helen |
forecasting?
Yes, you guessed right.
The function is FORECAST() -- David Biddulph "Helen" wrote in message ... Hello, Does Excel have a function that could help me predict or forecast, if you like, what week 7's number in below example could be? week1 11 week2 21 week3 35 week4 33 week5 39 week6 40 week7 ? Thanks, Helen |
forecasting?
On Mar 9, 5:44 pm, "Helen" wrote:
Does Excel have a function that could help me predict or forecast, if you like, what week 7's number in below example could be? week1 11 week2 21 week3 35 week4 33 week5 39 week6 40 week7 ? That really depends on your interpretation of the data. I suggest that you use the Chart Wizard to graph the data first. You can experiment with different Trendlines. Use Options to display the regression equation and the RSQ value. The closer to 1, the better the fit generally. But be sure to ask yourself: why does the data behave this way? For example, here is what I see -- an inherently flawed analysis because I know nothing about what's behind the numbers. The numbers show steep linear growth in weeks 1-3, but significantly flatter linear growth for weeks 3-6. Depending on your interpretation, FORECAST(7,B3:B6,A3:A6) might provide a better (linear) estimate of week 7 than FORECAST(7,B1:B6,A1:B6). Then again, based on your knowledge of information behind the numbers, you might decide that a parabolic equation (order-2 polynomial) yields a better fit to __all__ of the data together, demonstrating an expected down-turn in week 7. With Chart Trendline Options, you can extend the regression line "forward" to see the down-turn. In summary, using Excel functions to forecast blindly is likely to give you GIGO results -- "garbage in, garbage out". |
forecasting?
Blind use of RSQ can also get you into trouble. RSQ will continue to
increase as you add more polynomial terms, but you will quickly begin to overfit the data. Adjusted R-squared (deflated based on number of parameters fit) can be more informative and is given by the Regression tool in the Analysis ToolPak. I agree that there is no substitute for knowledge of the physical realities of the process that generated the data. A simple straight line clearly does not fit the entire data set very well, but its prediction (49.5 from FORECAST) is likely an upper bound for the week 7 value. A quadratic would predict a sharp downturn (36.7 at week 7, from TREND) that seems presumptuous in the absence of external knowledge of the physical system, and this is likely a lower bound. In between, there is little information in the numbers alone, to choose between more reasonable models such as linear in LOG(x), leading to 44.1 at week 7, or linear in SQRT(x), leading to 46.9 at week 7. Jerry "joeu2004" wrote: On Mar 9, 5:44 pm, "Helen" wrote: Does Excel have a function that could help me predict or forecast, if you like, what week 7's number in below example could be? week1 11 week2 21 week3 35 week4 33 week5 39 week6 40 week7 ? That really depends on your interpretation of the data. I suggest that you use the Chart Wizard to graph the data first. You can experiment with different Trendlines. Use Options to display the regression equation and the RSQ value. The closer to 1, the better the fit generally. But be sure to ask yourself: why does the data behave this way? For example, here is what I see -- an inherently flawed analysis because I know nothing about what's behind the numbers. The numbers show steep linear growth in weeks 1-3, but significantly flatter linear growth for weeks 3-6. Depending on your interpretation, FORECAST(7,B3:B6,A3:A6) might provide a better (linear) estimate of week 7 than FORECAST(7,B1:B6,A1:B6). Then again, based on your knowledge of information behind the numbers, you might decide that a parabolic equation (order-2 polynomial) yields a better fit to __all__ of the data together, demonstrating an expected down-turn in week 7. With Chart Trendline Options, you can extend the regression line "forward" to see the down-turn. In summary, using Excel functions to forecast blindly is likely to give you GIGO results -- "garbage in, garbage out". |
forecasting?
On Mar 10, 12:08 am, Jerry W. Lewis
wrote: Blind use of RSQ can also get you into trouble. RSQ will continue to increase as you add more polynomial terms, but you will quickly begin to overfit the data. Adjusted R-squared (deflated based on number of parameters fit) can be more informative and is given by the Regression tool in the Analysis ToolPak. I agree that there is no substitute for knowledge of the physical realities of the process that generated the data. A simple straight line clearly does not fit the entire data set very well, but its prediction (49.5 from FORECAST) is likely an upper bound for the week 7 value. A quadratic would predict a sharp downturn (36.7 at week 7, from TREND) that seems presumptuous in the absence of external knowledge of the physical system, and this is likely a lower bound. In between, there is little information in the numbers alone, to choose between more reasonable models such as linear in LOG(x), leading to 44.1 at week 7, or linear in SQRT(x), leading to 46.9 at week 7. Jerry "joeu2004" wrote: On Mar 9, 5:44 pm, "Helen" wrote: Does Excel have a function that could help me predict or forecast, if you like, what week 7's number in below example could be? week1 11 week2 21 week3 35 week4 33 week5 39 week6 40 week7 ? That really depends on your interpretation of the data. I suggest that you use the Chart Wizard to graph the data first. You can experiment with different Trendlines. Use Options to display the regression equation and the RSQ value. The closer to 1, the better the fit generally. But be sure to ask yourself: why does the data behave this way? For example, here is what I see -- an inherently flawed analysis because I know nothing about what's behind the numbers. The numbers show steep linear growth in weeks 1-3, but significantly flatter linear growth for weeks 3-6. Depending on your interpretation, FORECAST(7,B3:B6,A3:A6) might provide a better (linear) estimate of week 7 than FORECAST(7,B1:B6,A1:B6). Then again, based on your knowledge of information behind the numbers, you might decide that a parabolic equation (order-2 polynomial) yields a better fit to __all__ of the data together, demonstrating an expected down-turn in week 7. With Chart Trendline Options, you can extend the regression line "forward" to see the down-turn. In summary, using Excel functions to forecast blindly is likely to give you GIGO results -- "garbage in, garbage out".- Hide quoted text - - Show quoted text - Jerry, I understand how you got the Forecast & Trend Function(quadratic) fomulas If the data started in A1: =FORECAST(A7,B1:B6,A1:A6) and =TREND(B1:B6, {1;2;3;4;5;6}^{1,2},6^{1,2}) but I do not understand what formulas are you using for more reasonable models such as linear in LOG(x), leading to 44.1 at week 7, or linear in SQRT(x), leading to 46.9 at week 7? Could you please explain? Thanks in advance PJ |
forecasting?
If you plot the original data, and add a logarithmic trendline, that fits y
vs. LN(x). Or you can use =FORECAST(LN(7),y_data,LN(x_data)) Note that the prediction does not depend on the base of the logarithm, so you could just as easily use LOG() instead of LN() (it just changes the scale but not the shape of the presumed relationship). Also, this immediately adapts to SQRT() instead of LOG(). The basic idea is to slow the growth in x relative to y. Another common transformation that would shrink x a bit less is SQRT(x). To shrink x a bit more, you could try linear in 1/SQRT(x), leading to 41.4 at week 7. Linear in 1/x doesn't fit the data very well, but quadratic in 1/x does, leading to 42.1 at week 7 [ =TREND(y_data,1/x_data^{1,2},1/7^{1,2}) ]. The possibilities are limitless, for instance linear in x^c where -1<c<1. Without some knowledge of how x and y should be related, trying to make a prediction tighter than "somewhere between 39 and 49" is no more than a guess. Jerry " wrote: On Mar 10, 12:08 am, Jerry W. Lewis wrote: Blind use of RSQ can also get you into trouble. RSQ will continue to increase as you add more polynomial terms, but you will quickly begin to overfit the data. Adjusted R-squared (deflated based on number of parameters fit) can be more informative and is given by the Regression tool in the Analysis ToolPak. I agree that there is no substitute for knowledge of the physical realities of the process that generated the data. A simple straight line clearly does not fit the entire data set very well, but its prediction (49.5 from FORECAST) is likely an upper bound for the week 7 value. A quadratic would predict a sharp downturn (36.7 at week 7, from TREND) that seems presumptuous in the absence of external knowledge of the physical system, and this is likely a lower bound. In between, there is little information in the numbers alone, to choose between more reasonable models such as linear in LOG(x), leading to 44.1 at week 7, or linear in SQRT(x), leading to 46.9 at week 7. Jerry |
forecasting?
On Mar 10, 10:09 am, Jerry W. Lewis
wrote: If you plot the original data, and add a logarithmic trendline, that fits y vs. LN(x). Or you can use =FORECAST(LN(7),y_data,LN(x_data)) Note that the prediction does not depend on the base of the logarithm, so you could just as easily use LOG() instead of LN() (it just changes the scale but not the shape of the presumed relationship). Also, this immediately adapts to SQRT() instead of LOG(). The basic idea is to slow the growth in x relative to y. Another common transformation that would shrink x a bit less is SQRT(x). To shrink x a bit more, you could try linear in 1/SQRT(x), leading to 41.4 at week 7. Linear in 1/x doesn't fit the data very well, but quadratic in 1/x does, leading to 42.1 at week 7 [ =TREND(y_data,1/x_data^{1,2},1/7^{1,2}) ]. The possibilities are limitless, for instance linear in x^c where -1<c<1. Without some knowledge of how x and y should be related, trying to make a prediction tighter than "somewhere between 39 and 49" is no more than a guess. Jerry " wrote: On Mar 10, 12:08 am, Jerry W. Lewis wrote: Blind use of RSQ can also get you into trouble. RSQ will continue to increase as you add more polynomial terms, but you will quickly begin to overfit the data. Adjusted R-squared (deflated based on number of parameters fit) can be more informative and is given by the Regression tool in the Analysis ToolPak. I agree that there is no substitute for knowledge of the physical realities of the process that generated the data. A simple straight line clearly does not fit the entire data set very well, but its prediction (49.5 from FORECAST) is likely an upper bound for the week 7 value. A quadratic would predict a sharp downturn (36.7 at week 7, from TREND) that seems presumptuous in the absence of external knowledge of the physical system, and this is likely a lower bound. In between, there is little information in the numbers alone, to choose between more reasonable models such as linear in LOG(x), leading to 44.1 at week 7, or linear in SQRT(x), leading to 46.9 at week 7. Jerry- Hide quoted text - - Show quoted text - Thank you very much Jerry for the information. Much Appreciated! |
forecasting?
41
-- Gary''s Student gsnu200709 "Helen" wrote: Hello, Does Excel have a function that could help me predict or forecast, if you like, what week 7's number in below example could be? week1 11 week2 21 week3 35 week4 33 week5 39 week6 40 week7 ? Thanks, Helen |
forecasting?
On Mar 10, 9:25 pm, Gary''s Student
wrote: 41 -- Gary''s Student gsnu200709 "Helen" wrote: Hello, Does Excel have a function that could help me predict or forecast, if you like, what week 7's number in below example could be? week1 11 week2 21 week3 35 week4 33 week5 39 week6 40 week7 ? Thanks, Helen- Hide quoted text - - Show quoted text - I made a mistake on the formula above it should be =TREND(B1:B6,{1;2;3;4;5;6}^{1,2},7^{1,2}) Trend Function(quadratic) |
forecasting?
As always the answer is 42 <vbg
"Gary''s Student" skrev i melding ... 41 -- Gary''s Student gsnu200709 "Helen" wrote: Hello, Does Excel have a function that could help me predict or forecast, if you like, what week 7's number in below example could be? week1 11 week2 21 week3 35 week4 33 week5 39 week6 40 week7 ? Thanks, Helen |
forecasting?
A quick way to make a straight line forecast is to select the numbers
and pull down the fill handle in the bottom right corner of the selection. For an exponential trend, drag down with the right button and select growth trend from the shortcut menu. On Mar 10, 1:44 am, "Helen" wrote: Hello, Does Excel have a function that could help me predict or forecast, like, what week 7's number in below example could be? week1 11 week2 21 week3 35 week4 33 week5 39 week6 40 week7 ? Thanks, Helen |
| All times are GMT +1. The time now is 05:25 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com