i want to generate forecasting value using excel introp forecast function. I need to used excel forecasting linear function but I want to store forecasting value to Database.
I dnt know how to start Please help me for this.
Add a comment |
1 Answer
If all you need is a linear forecasting function, I think Excel Interop is probably overkill. You can add references to the Office COM libraries from your C# project and call the WorksheetFunction.Forecast function, or you could just add a method to do the same thing in your C# code directly. I think the linear forecast logic is essentially the following:
static double Forecast(double[] xValues, double[] yValues, double forecastPoint) { var xAverage = xValues.Average(); var yAverage = yValues.Average(); var bounds = yValues .Select((y, i) => new { Value = y, Index = i }) .Aggregate(new { Top = 0.0, Bottom = 0.0 }, (acc, cur) => new { Top = acc.Top + (xValues[cur.Index] - xAverage) * (yValues[cur.Index] - yAverage), Bottom = acc.Bottom + Math.Pow(xValues[cur.Index] - xAverage, 2.0) }); var level = bounds.Top / bounds.Bottom; return (yAverage - level * xAverage) + level * forecastPoint; } 4 Comments
Tanmay Nehete
thank you for reply but i have one one 2 colums one is with value having Manufacturing YTD-1Y and one is Year
Tanmay Nehete
so how to get forcating from this
Aaron M. Eshbach
Pass the values for the Year column as the
xValuesparameter, the values for the Manufacturing YTD-1Y as the yValues parameter, and the year you would like to forecast as the forecastPoint parameter.Aaron M. Eshbach
@Tanmay, I've updated the example. I often get the parameters to
Aggregate backwards, as I'm used to List.fold.