Multiple Regression using Categorical Variables

[j4k3fromstatefarm]

Hello. My question is very specific. I'm wanting to use two categorical predictors such as Month and Day of the Week to predict my Y values using a Multiple Linear Regression Equation. I have tried using FORECAST_LINEAR, but all of the variables within the formula require Double variables and I am using Variant variables in my code. The variant variables contain the matrices of the categorical predictors which only consist of 0s or 1s. There would be a 1 in a column if the specific x value is on that specific weekday or in a specific month. So, in total, every x should have two 1s and the remainder 0s. When I store these variables as doubles, it will not read them. Is there any possibility that I can forecast my y variable using 19 categorical predictors using the FORECAST_LINEAR approach or does anyone have any insight to what I could do?

NewY = WorksheetFunction.Forecast_Linear(x, known_y, known_x)

This is the equation in question. I do not have access to @Risk or any other excel add-ons. I am restricted to only using excel.

 

[Stevie]

Hi j4k3,
when I personally am trying to debug things like this, I try to go back to the simplest form and check that that is working as intended.
I would suggest manually loading some variant objects with known y and x values and doing a quick test in the form below:

Dim instance As WorksheetFunction
Dim Arg1 As Double
Dim Arg2 As Object
Dim Arg3 As Object
Dim returnValue As Double

returnValue = instance.Forecast_Linear(Arg1, Arg2, Arg3)

This is from the MSDN entry for the Forecast Linear function.

If this works for you, then it may show you the route to getting your specific case to work.
Without an example of your data inputs and setup, it is difficult for me to provide any further advice than that.

If this was helpful, please click 'Like!' in the bottom right.

Stevie ^.^