Calculating data based on a trend

[mac_xpert]

Hi All.

I have a access database that i am trying to modify to do Fee Forecasting.

The idea is to grab the details of the invoice dates and invoice amounts over the duration of previous projects. I have a pivot graph setup that plots this information (X - Project Duration) (Y - Percentage of total fee paid) and generates a trend line. As more projects are completed more data is fed into this graph and the trendline is recalculated.

Now want to use the trendline calculation to forecast the percentage of total fees paid at a given duration into any project. How can i grab the value of the trendline equation from the pivot graph? or is there another way of geting the trendline calculation, rather than grabbing it off of a graph?

thanks for any and all help!

Matt.

 

[DCrake]

Access isn't geared up to this type of data manipulation. Would it not be easier to post the data into a spreadsheet and let Excel do the trend analysis. This is the most economical way of doing it as Excel is geared up for this.

David

 

[mac_xpert]

I don't think this is beyond Access's capabilities with a little VBA I already have the graph and the trendline calculation being shown on it in access- all i'm asking for is a way to grab the trendline calculation off of the graph..

If i go out to excel it will become a manual operation and as the data the forecasting is based on changes all the time, i'd prefer it to be automatic.

Feel free to tell me i'm doing it all wrong if you can see a better way! - I'm sure i'm getting tunnel vision on this problem!!

 

[chergh]

In excel you do have the "linest" and "logest" functions available to calculate trendlines.

To get the trendline equation from a graph in excel you have to make sure the equation is displayed on the chart and:

ChartObject.SeriesCollection(1).Trendlines(1).DataLabel.Text

Will return the equation as a string.