How Do I Create Scatter Plots in Excel Using SigmaXL?
- Open Customer Data.xls. Click Sheet 1 Tab. Click SigmaXL > Graphical Tools > Scatter Plots; if necessary, click Use Entire Data Table, click Next.
- Select Overall Satisfaction, click Numeric Response (Y) >>; select
Avg Days Order Time to Delivery, click Numeric Predictor (X1) >>.
- Click OK. The resulting Scatter Plot is shown with equation, trend line, 95% confidence interval (blue lines – for a given X value this is the 95% confidence interval for predicted mean Overall Satisfaction) and 95% prediction interval (red lines – for a given X value this is the 95% confidence interval for predicted individual values of Overall Satisfaction).
- Now we want to redo the Scatter Plot and stratify by Customer Type. Press F3 or click Recall SigmaXL Dialog to recall last dialog. (Or, Click Sheet 1 Tab; Click SigmaXL > Graphical Tools > Scatter Plots; click Next.)
- Select Overall Satisfaction, click Numeric Response (Y) >>; select Average Days Order Time to Delivery, click Numeric Predictor (X1) >>; select Customer Type, click Group Category (X2) >>, Uncheck 95% Confidence Interval and 95% Prediction Interval. Click OK:
The equation is based on linear regression, using the method of least squares. R-squared * 100 is the percent variation of Y explained by X (here 10.3%).
Clearly, according to the analysis, Customer Type 3 is happier if orders take longer! But, does this make sense? Of course not! Customer Sat scores should not increase with Order to Delivery time. What is happening here? This is a coincidental situation. Something else is driving customer satisfaction. We will now look at the Scatter Plot Matrix to help us investigate other factors influencing Customer Satisfaction.
Tip: Be careful when interpreting scatter plots (and other statistical tools): Y versus X correlation or statistical significance does not always mean that we have a causal relationship. Umbrella sales are highly correlated to traffic accidents, but we cannot reduce the rate of traffic accidents by purchasing fewer umbrellas! The best way to validate a relationship is to perform a Design of Experiments (see Improve Phase).
Our CTO and Co-Founder, John Noguera, regularly hosts free Web Demos featuring SigmaXL and DiscoverSim
Click here to view some now!
Ph: 1.888.SigmaXL (744.6295)