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.

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...

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

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

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

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:

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?

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?

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

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?

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

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:

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.

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.

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.

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.

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?

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".

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.

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.

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

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.

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.