Revenue Trend

Topic

New Reply

Hi,
In Access 2000 I have a rollong 12 months revenue data for my accounts. I was asked to add a column that shows a trend. Up, Flat, or Down. No numbers, just an indicator.
I’ve used the trendlines in Excel but can’t seem to figure how to calculate for just an indicator in Access.

Any help woould be welcome
Thanks,
Scott

Reply | Quote

Replies

In Access 2000 I have a rollong 12 months revenue data for my accounts. I was asked to add a column that shows a trend. Up, Flat, or Down. No numbers, just an indicator.
I’ve used the trendlines in Excel but can’t seem to figure how to calculate for just an indicator in Access.

I would be against adding a column for this, as it seems like it should be calculated when you need it. But how do you calculate it? I haven’t seen anything like a “trendline” in Access. So what constitutes Up, Flat, or Down?

Reply | Quote

Mark,
I was thinking that similar to the trendline in Excel if the last point of the hypothetical trendline in my table is higher than the starting point the trend would be “Up” and vice versa.
But I can’t simply take the first and last month’s revenue and do a calculation because the months between them would not be figured into the trend.
Excel computes the values for the trendline taking all the months into consideration.

Scott

Reply | Quote

This is a linear regression exercise – I do not think Access has a function available, so you would have to build your own function.
It is similar to the SLOPE function in Excel. The formula is in the following link:

Excel Tutorial on Linear Regression
phoenix.phys.clemson.edu/tutorials/excel/regression.html

If VBA code is not your thing, you could do bits of this with update queries into a temporary table.
You would need to set a start date and an end date for the data set

Reply | Quote