# Dynamic Trend Regression Formula?

Jan 10, 2014
Ok so heres an example workbook:

Book5.xlsx

As you can see from the workbook, each column's amount of numbers depends on the data pulled from an index/match function.

These numbers will change and I want the TREND function to dynamically change with the data.

View 4 Replies
ADVERTISEMENT
Aug 5, 2008

1. I use a number of x,y pairs

2. From x,y pairs I produce "scatter" type chart

3. From the chart I get trend line and its equation e.g. 5-order polinominal

4. I convert equation into excel formula

5. I apply formula onto the same range of x argument and produce graph

6. The graph is very much different from the trend line!!! I would expect exact match as I use equation/formula for the trend line.

View 9 Replies
View Related
Nov 16, 2009

I'm trying to create a formula where I can show the trend of two values from one month to the next that shows wheather the results went up or down in a positive or negetive value. For example:

If I have a transfer % of 18% in October and November MTD is at 17% - the arrow would then reflect downward showing in green, as the lower percentage is better. but I need the arrow to show upward in Red if say the value for November MTD was 19% instead.

I tried conditional formatting and attempted an "If" formula. I must not have all the information to create the formula as it errors out on me. I can create it if only looking to reflect one arrow...

=if(c3<b3,"")

View 6 Replies
View Related
Jun 6, 2012

I am having trouble copying a charts trend formula into a cell.

the best I can do is select it as follows:

ActiveSheet.ChartObjects("Chart 4").Activate

ActiveChart.SeriesCollection(1).Trendlines(1).DataLabel.Select

I know you can work it out using linest, but this chart uses a polynomial to the degree of 3 to 5.

View 2 Replies
View Related
Oct 2, 2008

The above is a screenshot of the data analysis (regression) I want to automate with vba code. Like all macros, I tried to record first and only got the following

View 3 Replies
View Related
Jul 21, 2006

I was handed the attached file. understand everything except how the values in row 6 were derived No formula present when I received the file, just the numbers. Row 7 is hard entered scores the units achieved

View 8 Replies
View Related
Nov 11, 2008

I have the following dataset and was wondering how I can run a constrained regression in Excel with the constraint being that the total allocation of assets is 100%:

Total return (y): 12 data points

Asset 1 (x1): 12 data points

Asset 2 (x2): 12 data points

Asset 3 (x3): 12 data points

Asset 4 (x4): 12 data points

Asset 5 (x5): 12 data points

Asset 6 (x6): 12 data points

[Attached is a spreadsheet with the actual dataset]

I know the regression equation I need is R = b1X1 + b2X2 + .+ (1 - b1 - b2 -.- b5 )X6 + e

View 14 Replies
View Related
Jul 9, 2009

I am wanting to write a macro which uses the excel multiple regression function (a part of the data analysis add-in). I tried recording a macro while I selected the regression function (Tools> Data Analysis... etc.) which produced the following:

View 3 Replies
View Related
Oct 16, 2008

I would like to run a multiple linear regression in vba. I have one dependent and three explanatory variables. I will have to use a macro of some kind, since I need to run too many regressions to do it manually. To simplify things a little bit:

- There will always be exactly three independent variables

- There are no missing values

- The data is allways numerical

I've already got four ranges defined: Yrange, X1range, X2range, X3range. I would like to take these ranges as input parameters for the regression model. The only two parameters I need are Sum Square for Regression (SSR) and the degrees of freedom. I understand that you can use excel's matrix formulas to calculate some of the input parameters, but one doesn't really get around vba. Any (simple) source code allowing me to conduct a regression with three input parameters?

View 7 Replies
View Related
Feb 8, 2007

I am trying to set up the formula y = ax^2 + bx + c. Is there a function for that in excel?

To get a little more into my overall goal. I will have a x constant that will remain the same, but I have 8 different sets of a,b, & c coefficients. So, I would like to set up something like, if a row is labeled A1, find the A1 set of coefficients and use them in the quadratic equation. I was thinking I would need to use a CSE, is that correct?

View 3 Replies
View Related
Dec 19, 2012

I have one question about the regression. i know how can i do it lineair, exponentiel ... but how can i make a personnalised regression ? for example, if i have i function f(x)=a+b*x^2 or another function ... how can i introduce my function to draw the regression ? i have excel 2007

View 2 Replies
View Related
Feb 8, 2010

I am trying to do multivariate polynomial regression in excel, trying to correlate data of the form y=f(x1,x2) with second order polynomials:

Y = c + a1*x1 + a2*x1^2 + a3^x1^3 + b1*x2 + b2*x2^2 + b3*x2^3

Using the following command, I have achieved this:

=LINEST(A2:A10,B2:B10^{1,2,3,0,0,0}*C2:C10^{0,0,0, 1,2,3},TRUE,TRUE)

theory Excel uses for this kind of regression?

View 4 Replies
View Related
Jan 27, 2013

If I have a set of numbers 1,2,3,4,5,6,7,8,9 in cells, how can I get a line of best fit, linear regression, in the cells next to them?

View 1 Replies
View Related
Nov 20, 2013

Given 100 samples, to calculate: numbers of hours spent on study is dependent on numbers of hours spent on social networking site.

x (social networking site)

y (study)

x^2

xy

6

4

36

16

24

[Code] .......

SUMMARY OUTPUT

SUMMARY OUTPUT

Regression Statistics

Regression Statistics

Multiple R

0.571423290877713

[Code] ....

So, I've got the coefficient of correlation equals to 0.57, how can I plot my scatter graph?

View 1 Replies
View Related
May 5, 2014

I have set up a linear regression array in Excel and now want to test the significance of my r2 value at a certain level of significance. I've only been able to find tables that give the critical r value, but I want to test it at 99.73% level of confidence and none of them contain that specific value. Is there a way to do this in Excel?

View 3 Replies
View Related
Mar 2, 2009

I have a set of data. I know how to do linear regression over the whole set of data. How do I have another linear regression over the first 5 points in the set of data on the same graph ?? I am using Excel 2007

View 14 Replies
View Related
Apr 10, 2009

I'm trying to write a macro that will analyze data from one spreadsheet and do a regression. The information I want to be output on the same sheet. I tried to use the record function, but I got an error. It said "Run-time error '1004': ATPVBAEN.XLA could not be found. The code read:

View 2 Replies
View Related
May 23, 2007

to getting accurate regression coefficients from a dataset with *small* (standard) numbers, which contains centered predictors. More specifically, I have a dataset with 18 observed data points containing a criterion (y), a centered predictor variable (x), another centered predictor variable (z), and the interaction of the two centered predictor variables (xz). This multiple regression equation is structured to test for interactions between the two continuous predictor variables (x and z) as prescribed by Aiken and West (1991) in their classic book.

When I run the regression in Excel with the centered predictors, some of the regression coefficents in the output are estimated to be 0, although they are clealry *not* 0 as estimated by SPSS 14.0.2. I have spent many hours troubleshooting this problem (and searched many forums on the internet) and still do not know why this is happening.

Initially, I thought the problem might have to do with the cross-product of the centered predictors, but even just doing a regression with one of the centered predictors (for certain centered predictors) yields a regression coefficient of 0 (although it should be non-zero as per SPSS 14.0.2). When doing these multiple regressions with non-centered predictors, all regression coefficients are estimated accurately.

I was wondering if anyone had any insights on why I am experiencing these problems.... If anyone wants a sample of some test data I have used to troubleshoot these problems, you can download a file from: [url] and/or email me at (email deleted by Mod) for more datasets or questions.

View 5 Replies
View Related
Apr 10, 2013

However I have a subject at uni that requires me to create a series of regression models, histograms, correlation matrices etc.

For part of the assignment, i have to run 4 regressions (one for men & one for women) with dependent variable as average wages, and independent as bfast 1,2,3 and dinner 1,2,3 (all of which are dummy variables) (0 for male and 1 for female).

The second two regressions are exactly the same, except average wages must be transformed into log which i have already done.

I dont know how to differentiate the regression models into male and female (if possible). We must also include residuals and residual plots.

I keep getting an error that says non-numeric data, the other says input range must be a contiguous reference.

View 1 Replies
View Related
Jan 6, 2009

I have two columns where TREND does not work well. I want to calculate the TREND value of Column 2 (PERCENTILE RANK) based on a new RAW number. If I TREND between just the two rows, the value is fine (i.e. If I TREND between 105 and 110 RAW, I get the right PERCENTILE RANK if the new X value is 106 (which is 15.3% PERCENTILE RANK). How can I make a formula that will only look at the two rows and TREND just those two rows to give me the right PERCENTILE RANK.

RAW PERCENTILE RANK

10514

11020.4

11121.8

11729.5

11833

12342.5

12444.5

12954.4...........

View 5 Replies
View Related
Apr 2, 2009

I'm trying to figure out how to do seasonal forecasting based on volumes. I tried to use FORECAST but the numbers are not accurate.

I have actual data to work with to come up with my forecasting on a monthly basis. Our yearly calender is from Nov 08 to Oct 09.

Here's a sample of the data....

View 12 Replies
View Related
Jan 12, 2010

I have a simple x y scatter graph, to which i have a trend line running through it, fine.

what i need to do is fine some values according to that trend line. e.g. my X axis goes up to 60 and my Y axis goes to 10, so if i draw accross at 55 from the trend line, i then go down to a number on my Y axis.

View 14 Replies
View Related
Dec 14, 2008

What I have is a column (column A) that only has two data entries at any one time.

I need column B to fill in the trend of those two data points.

My problem is I don't know how to deal with the cells that say #N/A. Which is every cell other than the two cells with data.

If I replace the #N/As with say 1, then it messes up the trend data points.

It seems like I need to be using the trend command, but any solution would be welcome.

View 14 Replies
View Related
Nov 23, 2011

When using the Trend function, I can get the value for individual future periods. I then can get a total for the year by summing each individual future period.

What I don't know how to do is sum them all using one formula. For instance, If I know the values of periods 1-5, how would I write a formula that would sum the future values (in one cell) of periods 6-12 -- without having to sum each future period individually?

View 3 Replies
View Related
Sep 6, 2006

I am trying to use VBA to find a value on a trendline. I have been unable to call the TREND function from Excel, and I can't see what I'm doing wrong. I'm using the following line of

point25 = WorksheetFunction.TREND( Range("d2:d25"), Range("c2:c25"), 25)

I keep getting "Runtime error 1004 - Unable to get the Trend property of the WorksheetFunction class".

View 9 Replies
View Related
Oct 5, 2006

I need to analyse trends between a range of cells. The idea is to display an image of a triangle pointing up if the percentage between the current month and the previous is bigger, a triangle pointing down if the percentage has decreased or a rectangle if both months are the same. I've tried to build a userform to ask the user to input the range and then analyze the data...with no luck at all.

View 9 Replies
View Related
Jan 4, 2007

I am using the Forecast formula to give me a value from a Linear trend. However I need to limit the resulting value that is displayed within a min and max value. Background: This is to allow me to calculate the amount of bonus someone will earn depending on the Percentages of their target they achieve. The min bonus is 25% when 80% of target is achieved and max bonus is 100% when 125% or greater of target is achieved.

So the Forecast formular works great using the existing values, but i need help limiting the results to between 25% and 100%. I was trying to use a sumif formula to copy the resulting cell but only if the value fell between the 25 and 100%. I've attached the spreadsheet as I think it will highlight what i'm trying to do better than I can explain it.

View 2 Replies
View Related
Mar 3, 2007

I am trying to use the TREND worksheet function on a vba array. I keep getting Runtime error 1004: Unable to get the TREND property of the WorksheetFunction Class. I can bypass the error (like it were a search with no result), but I get no result of any kind from the function. My search of other threads yields nothing concrete, but causes a guess that it may have to be used on a worksheet range not a vba array. But that's not actually stated in any of the threads, and I find no documentation to that effect. Is this the issue or is it something else altogether?

Sub test()

Dim knowny As Variant

Dim knownx As Variant

Dim newx As Integer

Dim myval As Integer

knowny = Array(2, 4, 6, 8, 10)

knownx = Array(1, 2, 3, 4, 5)

newx = 6

'On Error Resume Next

myval = Application.WorksheetFunction.Trend(knowny, knownx, newx)

' 'next error, err as normal.

' On Error GoTo 0

' Err.Clear

Debug.Print myval

End Sub

View 5 Replies
View Related
May 31, 2007

Ive got an inventory of about 2000+ items as well as items that can be broken down into more specific groups such as colors, designs and sizes. What i want to know is how to get the correlation, thats excel function CORREL(), of each item for the time weve had it. The data I can provide is the monthly quanitity sold of the item.

For example I have item A.

Sold Month

5 1

7 2

4 3

5 4

and so on. Easy enough to do. My only problem is I would have to manually do that 2000+ times for my entire inventory to figure that out. What I wants is a command that can verify my item code, which is simple enough as I can convert the item code to something as simple as a string or leave it as is. Not every item has been in the system an equally long time though. If that were the case i could just copy it with blank cells to fill the space and copy and paste all the way down. So I need to find a way to get an IF statement to correlate the corresponding cells IF the item number matches the target cell. What I have right now is something to this extent.

=IF('2'!$A:$A,A1,CORREL('2'!$B:$B,'2'!$E:$E))

View 4 Replies
View Related
Dec 5, 2007

On top of these graphs, i draw a line (insert a line shape) corresponding to a certain gradient in the data presented. I do not want this line to be exact (i.e. not point to point) as it is best to be drawn by eye so that the general 'gist' of the data can be found. What i would like, using any method available, is to be able to find the gradient of this line i have just drawn which obviosuly must be done taking into account the scale of the graph. The scales of the graph will not be consistent across all applications.

View 4 Replies
View Related