Forecast throught multiple sheets

I'm not understanding. Please provide the solution you wish to see (manually entered) so that we can understand what you wish to accomplish. And perhaps a bit more data so that we can see a pattern.

Your second upload looks the same as the first one. Three sheets with no solution shown.

I'm still not getting it. What is your logic for forecasting? Please explain in simple language. You seem to have just increased the value of each sheet by one and that does not seem to make sense to me. Perhaps a real life example.

My apologies, I thought that you were attempting to build a forecast sheet based upon historical values. I did not understand that you were attempting to use the Forecast function of Excel. This is because I have never had use for this function. The best I can do is offer up this site http://www.techonthenet.com/excel/formulas/forecast.php, but I don't really know if that will help. I will seek additional help for you on this issue.

Hi,

I'm afraid it's still not clear what you want.

In which cells and in which sheets exactly will the known_ys and known_xs be? Will there just be one per sheet? Will it always be in the same cell in every sheet? Where will the x value be?

Regards

Since the FORECAST() function does not support 3D referencing, I'm afraid I don't see a "single step" way to get what you want (see here for an explanation of 3D references and a list of functions that can use 3D references: http://office.microsoft.com/en-us/ex...010102346.aspx). I see two possible approaches to this problem:

1) Using functions (INDIRECT() function might be particularly useful, though I personally only use it as a last resort: http://office.microsoft.com/en-us/ma...778.aspx?CTT=1), "copy" each entry from each position in each sheet into a single spreadsheet. Once everything is in neat columns in a single sheet, then you can use FORECAST(), TREND(), or LINEST() to perform the desired regression. I would expect this table would look something like this:

Please Login or Register  to view this content.

Then you can enter =FORECAST(known_y's, known_x's,new_x) referencing this table.

2) a) I could also see doing this using Solver to numerically create a linear least squares regression. On the "output" sheet, add a cell for m and b (regression equation is y=mx+b) and put reasonably guesses for these values in these cells.
b) in each spreadsheet, calculate y for each date, then the deviation for each point on each spreadsheet (=known_y-equation_y)^2
c) in the output spreadsheet, you can now use a 3D reference in the SUM() function to sum these deviations.
d) Call Solver, tell it to set this sum to a minimum by changing m and b.
e) Using m and b, you can calculate your forecast values.