The independent variable is the cause.
The mediator explains the relationship between the IV and DV.
The moderator affects the strength of the relationship between IV and DV. It provides a condition; "only when this happens that will happen".
The dependent variable is the effect.
This is a guide to walk you through how to analyze survey data in Excel, covering key statistical tests such as regression, ANOVA, mediation, and moderation analysis!
Step 1: Data Preparation
Before running any tests, ensure your data is clean and organized. Go through the results of your survey and:
Remove incomplete responses or errors.
Label your variables (IV, DV, Mediator, Moderator) clearly.
Ensure your data is in numerical format.
Calculating Averages (Means) in Excel
Select the cell where you want the average displayed.
Type =AVERAGE (range) where "range" is the cell range of your data.
Press Enter.
Example: If your data is in cells F2 : F50, type =AVERAGE (F2 : F50).
Step 2: Running a Simple Regression Analysis
Regression helps you understand the relationship between variables.
Performing a Regression Test in Excel:
Go to the Data tab > Click Date Analysis.
Select Regression and click OK.
For Input Y Range, select your dependent variable.
For Input X Range, select your independent variable.
Click OK to generate the results.
Key Results to Analyze:
R-squared: Shows how well your independent variable explains the dependent variable.
P-value: Determines if the relationship is statistically significant (p < 0.05 means significant) (Just like you learned in DS 123!).
Video Guide:
Step 3: Conducting ANOVA in Excel
ANOVA (Analysis of Variance) is used to compare means across multiple groups.
Running ANOVA in Excel:
Go to Date > Data Analysis.
Select ANOVA: Single Factor.
Enter your data range.
Click OK.
Interpretation: A low p-value (< 0.05) suggests significant differences between groups.
Step 4: Mediation Analysis in Excel
Mediation examines if a third variable (mediator) explains the relationship between an independent and dependent variable.
Steps for Mediation Analysis:
Run three regressions:
IV → DV
IV → Mediator
Mediator + IV → DV
If the effect of IV on DV weakens after including the mediator, mediation is present.
Step 5: Moderation Analysis in Excel
Moderation tests whether the strength of the relationship between two variables changes depending on a third variable.
Steps for Moderation Analysis:
Create and interaction term: Multiply the moderator and independent variable.
Run a regression with:
IV
Moderator
Interaction term
A significant interaction term indicates moderation.
Step 6 (Final Step): Interpret Your Findings!
Once you've completed the analysis, focus on interpreting the results:
Look at p-values to check for statistical significance (typically p < 0.05).
Examine coefficients to understand the strength and direction of relationships.
Analyze R-squared to see how well your model explains the variability in the DV.
Key Questions:
Does the mediator explain part of the IV-DV relationship?
Does the moderator change the strength of the relationship between the IV and DV?
Are your results statistically significant?