• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

MULTIVARIATE REGRESSION ANALYSIS VBA

A

anup47

Member
https://spreadsheets1.google.com/spreadsheet/pub?hl=en&hl=en&key=0AsicBmCuaAIDdEh6blFKSmczQzRqbl9XU3dkNEN5dUE&single=true&gid=0&output=html


Dear All

I have pasted the link for my spreadsheet.

Here i want a vba for multivariate regression analysis for X1, X2 and Y1.

There are lot of colums foy y variable (y1 y2 y3 ....). I need a customize vba macro such that it can be used for columns containing y2, y3, y4 .... and so on.(keeping X1 and X2 constant).


with regards

Anup
 
Anup

I discuss doing Multiple Variable Linear Regressions using the Linest function about 2/3rds down the following post:

http://chandoo.org/wp/2011/01/26/trendlines-and-forecasting-in-excel-part-2/


I Don't know why you'd need to use Macros for that?

Once it is setup it is easy to adjust the columns for another set of data or copy the first setup and change for the second set etc


Assuming your Data starts in A1

You would use for Y1 a

=LINEST(D2:D28,A2:B28,TRUE, TRUE)

Array entered into a 3 Column 5 Row area


Then repeat for the next Y2

=LINEST(E2:E28,A2:B28,TRUE, TRUE)

Array entered into another 3 Column 5 Row area etc
 
Thank you for your reply.

when i use this linest function as an array, i get my Beta1, Beta2, alpha (i.e coefficients of x1 x2 etc) in horizontal format. Now i have Y1 to Y500 columns against 2 columns x1 and x2. I want that beta 2 , beta 1 and alpha along with r2 all should come below respective Yi columns. And since the columns are more in numbers i want to use a macro, if you can provide some formula that can directly be used to get the desired result.


with regards

Anup
 
Anup


I have a better idea than VBA


Data Tables !


Setup a Data Table with

1-500 as values down the side

Across the Top put your Parameters Beta 1, 2, r2 etc

Setup a Linest array area (Ctrl Shift Enter) and use an equation which includes an offset for your Y range

like =LINEST(OFFSET($C$2:$C$28,,$B$30),A2:B28,TRUE,TRUE)

Then in B30 put a value of 1


Now use a Data Table with a Column Input cell of B30


Voila, 500 Results


Enjoy


Here an example with your data: https://rapidshare.com/files/2500942977/Anup47.xlsx
 
I can suggest a company "Informatics Outsourcing" is one of the leading Market Research Company. They are providing best Regression Analysis service Worldwide.
 
Hey hui


can u tell me how did you prepare the table in the excel spreadsheet anup.47


I'm using it and have no difficulty but when i'm trying to construct the table, i'm unable to do it. Please explain me how can i make the table (below m2 and m1)


Regards

anup
 
Hi Hui


I'm trying to develop a financial model in which your post has helped me a lot.

Now, again I'm uploading a file : https://docs.google.com/spreadsheet/ccc?key=0AsicBmCuaAIDdEV4UDVocW1UUVhDRGxBTm54ZjVPSmc&hl=en_US#gid=0


Here I've used Data table and could make what i wanted to make.

Now when you'll go through the sheet you'll find that when you change cell c271 there will be change in U285.


So, now i need to make table for all the different values in c271 and obtain all the different results in cell U285.

I've tride data table but its not working, i think i've made it more complex as cell c271 and cell b273 are linked.


Please help me out of this problem.


I also need to mention that when i was uploading the sheet some formulae changed, when you download it you might need to change it. for e.g. formula copy g279 and paste in g275 to g278. I don't know why it is showing in the formula continue.


Regards

Anup
 
sorry hui

ok i repeat my question.


I'm trying to develop a financial model in which your post has helped me a lot.

Now, again I'm uploading a file : https://docs.google.com/spreadsheet/ccc?key=0AsicBmCuaAIDdEV4UDVocW1UUVhDRGxBTm54ZjVPSmc&hl=en_US#gid=0


Here I've used Data table and could make what i wanted to make.

Now when you'll go through the sheet you'll find that when you change cell c271 there will be change in U285.


So, now i need to make table for all the different values in c271 and obtain all the different results in cell U285.

I've tride data table but its not working, i think i've made it more complex as cell c271 and cell b273 are linked.


Please help me out of this problem.


I also need to mention that when i was uploading the sheet some formulae changed, when you download it you might need to change it. for e.g. formula copy g279 and paste in g275 to g278. I don't know why it is showing in the formula continue.


Regards

Anup
 
Getting this message

===

You need permission to access this item.


You are signed in as i*******on@gmail.com, but you don't have permission to access this item. You can request access from the owner or sign-in as a different user. Learn more


Sign in as a different user


---

I have sent you 2 Access Requests via Google ?
 
Its not the Data Table its your logic ?


In E271 I put =U285

and manually put values in C271

E271 never changes ?


also copy the format from U289 down
 
hui

please give me your email id i'll send u my file, i don't know why when i'm uploading it some formulae are getting changed.


Regards

Anup
 
@Anup


Please note that all the members of our forum work out of their love for Excel and their passion to share what they know. So we request you to not urge anyone for immediate help or things like that. Instead be patient. If you want urgent help, this is not the place to be.
 
Hi Hui,

I was just thinking if my problem can be solved by using some macros, in which for all the different values in c271 we will copy and paste the results of cell U285 and make a table out of that.


Regards

Anup
 
Hi hui

I've tried scenario manager and i've seen its working, but the problem is its very much time taking, I've to add 100's of scenario to back test the results. Is there a shortcut way in scenario manager where for every 1 day rise in cell c271 we get scenarios and get results for cell u285.


Regards

Anup
 
You must log in or register to reply here.
Back
Top