Join Curt Frye for an in-depth discussion in this video Identifying relationships using XY scatter charts, part of Learning Excel Data-Analysis.
- One common goal of performing business data analysis is to discover relationships between values. For example, you might own a store and want to determine whether individuals who come to the store from farther away are more likely to spend money once they get there. If you've collected that data, you can summarize it visually using what's called an XY Scatter Chart. In this movie I will show you how to create one. I have two columns of data.
Column A has the distance traveled to the store. Column B has the amount spent. I have 26 rows of data. To create my XY scatter chart I need to select that data. So I'll select from Cell A1 down to B27. Then on the Insert tab of the Ribbon, I'll click the Insert Scatter button, and click the first Scatter Chart subtype.
So you can see that what I've done is create a chart that maps Amount Spent to Distance Traveled. Amount Spent is on the vertical axis, and Distance Traveled is on the horizontal axis, in this case. There does appear to be a clear trend. Customers who travel a short distance tend to spend less money, and those who travel longer distances, as you can see from this upward trend, tend to spend more money when they visit the store. That makes sense.
If you live close to a store, you are more likely to make frequent small purchases every time you go as opposed to perhaps a destination shopping place, such as a large department store that you don't have in your hometown. You're more likely to spend more money when you finally do get there because you don't want to have to repeat the trip. That is the sort of insight that visualizing your data using an XY Scatter Chart can bring.
Lynda.com is a PMI Registered Education Provider. This course qualifies for professional development units (PDUs). To view the activity and PDU details for this course, click here.
The PMI Registered Education Provider logo is a registered mark of the Project Management Institute, Inc.
- Calculating mean and median values
- Analyzing data using variance and standard deviation
- Minimizing errors
- Visualizing data with histograms, charts, and more
- Testing hypotheses
- Measuring covariance and correlation
- Performing Bayesian analysis