Forecast throught multiple sheets

[a1b2c3d4e5f6g7]

Hello,


In the attachment i made an example. I need to forecast the Type a/b/c. How do you write the formula through these multiple sheets (x1 and x2). So the known x's are x1 and x2. And X is x3.
The known y's are in the cell's (b3, c3, d3).

[alansidman]

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.

[a1b2c3d4e5f6g7]

Okay.

I have got 2400 sheets.

In these 2400 sheets, you've got the same table.
From all these tables i want to calculate the forecasting formula from every cell.

In the attachment i made for simplicity just three sheets. In every sheet the same table, a single cell table.

So in the fourth sheet i need the formula of 'forecast'. So the outcome would be 5. But keep in your mind i've got 2400 sheets instead of just four.

So the x's are 1 to 4. The known y's are in the tables.

[alansidman]

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

[a1b2c3d4e5f6g7]

This should be the one

[alansidman]

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.

[a1b2c3d4e5f6g7]

I made a simple example. So i increased every sheet by one.

You don't need a real life situation. I just need the construction of a forecast formula through multiple sheets. The outcome is in the fourth sheet.

See attachment. This time the x's are dates. Because it is every week, you could say the x's are (1, 2, 3)

[alansidman]

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.

[XOR LX]

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

[MrShorty]

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.