Experts:
I need some assistance with *REGRESSION* analysis in Excel. While I have some fundamental understanding of how (linear) regression works, I do need some clarification on both a) interpretation and b) potentially restructuring the sample data. Allow me to provide some background on the XLS (attached) and process first.
Sample File:
- Sample file includes purely made-up/random data; however, it mimics my organization's actual concept.
- Attached XLS includes 3 tabs:
1. "SampleData" -- contains 2 columns (Crime and State)
2. "SampleData_Recoded" -- given the error (i.e., "Input range contains non-numeric data"), I re-coded the string values into numeric values (see tab "Lookup")
3. "Lookup" -- summarizes the re-coding from string to numeric value
Regression Process:
- On tab "SampleData_Recoded", the SUMMARY OUTPUT is included (starting in column P)
- Based on the regression analysis, the "Coefficients" column gives me both the intercept (3.029850746) and State value (0.179104478)
Regression Model (based on the Coefficients information):
- Cell P23 includes a drop-down list (1, 2, 3 indicating CA, FL, and TX, respectively)
- Cell Q23 uses the formula which **predicts** the dependent variable (i.e., Crime).
- Given cell Q23 includes decimals, I am using cell R24 to round to 0 decimals.
- Lastly, cell S23 uses the INDEX/MATCH to cross-reference the numeric value to the crime (string).
My question(s):
1. Based on this (small) **sample** data set, does it mean that the most prevalent crime = "Homicide" if CA (1) OR FL (2) is selected in cell P23?
2. Alternatively, does it also mean that "Kidnapping" is the most frequently occurring crime if TX (3) is selected in cell P23?
3. If my interpretation of Q1 and Q2 are incorrect, what does the regression (model) tell me in this example?
4. Finally, is there another way to restructure the data set and perform the regression differently so that I get some form of matrix (for each state) and their respective crimes?
Thank you for your help in advance,
EEH
Bookmarks