Exam 2 – Troubleshooting
March 9, 2015
Here are a few common issues people are running into:
- When creating Dummy variables in Excel many are using the IF function but not getting it to work. You need to format the statement with double quotes around the neighborhood name like the following: =IF(G2=”Eastside”,1,0)
- Non-numeric data found and LINEST() errors seem to be related to the use of Filters in combination with the Regression function. To remedy, follow these steps.
- Copy your data
- Paste (values only) into a brand new worksheet
- Re-run regression
Please let me know if there are other issues to post here.
This afternoon you should me a formula for 2015 forecasting that referenced a dummy for the neighborhood that I chose, could you show me that formula again? This is in reference to the y equation.
Let’s say your model uses Square Footage (SqFt), Beds, Baths and two neighborhood “dummy” variables, “Aberdeen” and “Westside”.
And, the coefficients produced by your final model are as follows:
This would translate to a regression equation, as follows (* means multiply):
y = 49.7*SqFt + 4189.5*Beds + 16001.2*Baths + 12499.4*Aberdeen + (-15043.8)*Westside + 2377.2
To use the same equation to generate a forecast you would plug in the values for each variable. For example..
SqFt=1500; Beds=3; Baths=2; Aberdeen=1; Westside=0
y = 49.7*(1500) + 4189.5*(3) + 16001.2*(2) + 12499.4*(1) + (-15043.8)*(0) + 2377.2
y = 74550 + 12568.5 + 32002.4 + 12499.4 + 0 + 2377.2
y = 133997.5
In other words, the forecast for a 1500 sqft, 3 bed, 2 bath house in the Aberdeen neighborhood would be $133,997.50.
Hope this helps!