Hello everyone,
I hope someone is out here who can help me with a simple calculation...
I have a sales data base in access with monthly sales history by
product. to make future predictions I need the slope (representing
growth) of monthly sales.
the data is setup in a crosstab with the months in different collums,
say column 1 to 12 for last year. I want to calculate the slope for
every row (on the same query or another if needed)
Does anyone have an idea how to calculate the slope of the trendline
for those values? In excel I can just use =slope(known y's, known x's).
the slope i need is from the trendline in common statistics (I believe
it is calcuated by minimizing the sum of the squared deviation - OLS)
I cannot use an ordinary excel export (too many lines).
Anyone an idea how to work around that?
Help is much appreciated,
Thanks
Kai 4 22610
In your case, if the months are m=1,2...12 and, for each record
you have the 12 observations: y(1)...y(m)...y (12),
the slope is given by:
[ sum for m = 1 to 12 of (m-6.5) * y(m) ] / 143
PS
In the particular case when y(m) = b * m for each m,
you should get slope = b where b is any real
-tom
kux ha scritto: Hello everyone, I hope someone is out here who can help me with a simple calculation...
I have a sales data base in access with monthly sales history by product. to make future predictions I need the slope (representing growth) of monthly sales. the data is setup in a crosstab with the months in different collums, say column 1 to 12 for last year. I want to calculate the slope for every row (on the same query or another if needed)
Does anyone have an idea how to calculate the slope of the trendline for those values? In excel I can just use =slope(known y's, known x's). the slope i need is from the trendline in common statistics (I believe it is calcuated by minimizing the sum of the squared deviation - OLS)
I cannot use an ordinary excel export (too many lines).
Anyone an idea how to work around that?
Help is much appreciated,
Thanks Kai
Hello Tom,
thanks a lot for that formula. It works great with the data i checked.
but how do you get to 143?
I need to do similar calculations with other time frames as well (24,
36 and 48 months). I could always try it out but I would rahter enter a
formula in case someone changes the lenght of the period later...
THanks
Kai
Hi Kai, sorry for ignoring your second question, I missed it.
I hope I got it right (I have done some quick hand computation)...
In a more general case, where one has H consecutive equispaced time
occasions
y(1)...y(m)...y (H), the slope should be:
[ sum for m = 1 to H of (m - (H+1)/2) * y(m) ] / D
where: D = H * (H ^ 2 - 1) / 12
or, if you prefer: D = H * (H + 1) * (H - 1) / 12
-tommaso
PS
let me know if it works fine
Kux ha scritto: Hello Tom, thanks a lot for that formula. It works great with the data i checked.
but how do you get to 143? I need to do similar calculations with other time frames as well (24, 36 and 48 months). I could always try it out but I would rahter enter a formula in case someone changes the lenght of the period later...
THanks
Kai
Hey Tom,
to be honest, I dont understand why the formula looks like it does. but
it WORKS!
....and in the meantime I found that it is also the sum for m = 1 to 12
of (m-6.5)^2. (you can replace 12 with H and 6.5 with (H+1)/2)
That holds true at least for the values I checked. I would be surprised
if that is a coincidence but could not say why it works.
thank you so much. That completely solved my problem.
kai This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Subrahmanyam Arya |
last post by:
Hi Folks ,
I am trying to solve the problem of reading the numbers correctly
from a serial line into an intel pentium processor machine .
I am reading 1 byte and 2byte data both positive and negative
quantities .
Can any one tell me what are the things i should bear in mind .
|
by: BCM |
last post by:
I've been asked to display some meter data on the web and I need preliminary
advice.
We have several hundred meters on site, with a system that writes out xml
files at 10 second intervals containing their data. (These files are
overwritten every 10 seconds, not appended to.)
First, I need to display two numeric pieces of this data just as numbers in
a web page. Naturally that's easy enough: where I'm stumped is REFRESHING
that data...
|
by: Joe |
last post by:
I need to add a trend line to a scatter plot (not automatically supported by
the charting package) but I don't know how to calculate it. Is there any
formulas around for calculating what the trend line should look like?
|
by: kux |
last post by:
Hello everyone,
I hope someone is out here who can help me with a simple calculation...
I have a sales data base in access with monthly sales history by
product. to make future predictions I need the slope (representing
growth) of monthly sales.
the data is setup in a crosstab with the months in different collums,
say column 1 to 12 for last year. I want to calculate the slope for
every row (on the same query or another if needed)
|
by: kux |
last post by:
Hello everyone,
I hope someone is out here who can help me with a simple calculation...
I have a sales data base in access with monthly sales history by
product. to make future predictions I need the slope (representing
growth) of monthly sales.
the data is setup in a crosstab with the months in different collums,
say column 1 to 12 for last year. I want to calculate the slope for
every row (on the same query or another if needed)
| |
by: bubbles |
last post by:
Access 2003 front-end with SQL Server 2005 backend.
I have charts in the Access application, and these charts have
polynomial trend
lines.
Is there a way, programmatically, to define the polynomial trend line
periods?
Thanks,
|
by: ellisrj |
last post by:
Hi guys, I'm trying to find the slope of a trend line that best fits a bunch of X and Y points, as per this thread:
http://www.thescripts.com/forum/thread472629.html
The difference being that I want my equation in the form Y = AX^B
I don't have the mathematical skill to be able to do this, would anybody like to have a go at the process shown in the discussion referenced above?
Regards
|
by: ashwini1680 |
last post by:
is there any function in excel to find slope of a straight line iin excel?
what's it? Suggest a site or book for studing excel.advance thanks.
|
by: whnkee |
last post by:
I need to audit the freight bills charged by freight company which has
over 20,000 consignment per week. The basic charge is base on distance
and weight, something like this:
SYD-MEL <1kg $6
SYD-MEL<3kg $6.7
SYD-MEL<5kg $7
SYD-MEL<10kg$10
SYD-MEL>=10kg $12, $4 per kg rate
SYD-PER<1kg $10
......
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |