Help with Regression Analysis

[CY078]

Hi All

I'm new to this concept of regression but I understand the basics. I'm using XL to conduct the analysis.

The end result is to forecast the automotive market. I've split my analysis at a couple of points (GFC, Japan Tsunami, COVID) to ensure the data is true.

My issue is in the first two splits the R value comes back reasonable ... but the third split the R values come back really small. I have tried over 10 different analysis and the pattern is the same.

Upon searching I now realised I should not have used data that is too lineal / structured (ie: Population growth) because it skews the outcome.

So I just wanted to ask the group
1) Have I made some kind of PEBKAC mistake using the XL function ?
2) Other advice as to why the R value is so low in the third split ?
3) Other advice as to how I can improve my analysis ?

Loading Google Sheets

Above is the link to the file

Shout out if there are any questions

Thanks

 

[6StringJazzer]

I don't see how this description maps to your file. Please be specific about what you mean by "first two splits" and "third split" and where they are in your file. How did you perform the regression?

Just in general, a low R² may mean either that you have chosen the wrong way to model the data (e.g., doing a linear regression when the data is actually polynomial) or the data is just random and does not fit a trend.

 

[CY078]

Apologies .. i'll try and explain it better.

Background

In each of the regression tabs (blue tabs) i've split the regression in line with the below colour coding and respective rows (middle col). The col on the RHS is just where I put the outcome so the formulas automatically calculate.

Split 1: Stops @ row 28 in order to get a better fit for $M$30:$M$33 = 26 Observations (GFC affected)
Split 2: Stops @ row 39 in order to get a better fit for $M$40 = 37 Observations (Japan Tsunami affected)
Split 3: just calculates the whole data set (ie: 81 observations = Cell 55)

The Y axis is Col H (SAAR of the Market)

The X axis is Col I:K (I only used L for one of the regressions)

The outcomes are pasted in Col Y:AG

I performed the regression by Data>Data Analysis>Regression

Issue

My issue is that the first two outcomes (yellow & green split) have a reasonable R factor ... but when I get to the final outcome it is very low

So
1) Is it something I have done ?
2) It's just the data > if so what is the best way to analyse the data ?

Thanks

 

[CY078]

Anyone ??