How Do I Create Scatter Plots in Excel Using SigmaXL?

Scatter Plots

1. Open Customer Data.xlsx. Click Sheet 1 Tab. Click SigmaXL > Graphical Tools > Scatter Plots; if necessary, click Use Entire Data Table, click Next.


2. Select Overall Satisfaction, click Numeric Response (Y) >>; select Avg Days Order Time to Delivery, click Numeric Predictor (X1) >>. Check Trendline, 95% Confidence Interval and 95% Prediction Interval as shown:

   
   

   
   
3. 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).



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%).


4. 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.)


5. 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:



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).