Whenever you collect data in Microsoft Excel or another program, you should always examine it for trends. In this video, learn how to examine your data visually by adding a trendline to a chart.
- [Instructor] Whenever you collect data in Microsoft Excel or another program, you should always examine it for trends. In this movie, I will show you how to examine your data visually by adding a trendline into a chart. My sample file is the Trendline workbook, and you can find it in the Chapter02 folder of your exercise files collection. This workbook contains a set of data which indicates the distance that customers drove to get to a store. In the second column, we have the corresponding amount that they spent on that particular trip. So in the first case, which is in row two, you can see that a customer drove one mile and spent $35.
I created an XY scatter chart to map the distance traveled to the amount spent. And I used an XY scatter chart because I have two numerical values. If I had, say, a state that a customer came from, then I would use columns, or perhaps bars, or another indicator to map a particular state to the total amount spent. But let's say that I want to create a trendline that best fits the data that appears within my chart.
So that if I said, okay, let's say someone drives 110 miles, how much would they spend? I can see that visually by adding a trendline. So I will click the chart to select it. And then on the right side of the chart, I will click the Chart Element button. The Chart Elements panel appears. And I will point to Trendline, click the right-pointing triangle that appears beside it, and then I will click More Options. I can use the controls in the Format Trendline task pane to change my chart, specifically the trendline.
Under Trendline Options, you see that I have a number of data types or trendline types that I can use. If you don't know what kind of data you have, always assume it's linear unless you are told differently. So we'll have Linear selected there. Then I'll scroll down and under Forecast, you can either leave the trendline as is, or you can forecast forward a couple of periods. In this case, I don't have time-based data, I just have distance versus amount spent, so I'll leave it as it is.
But I did want you to see how those values work within this dataset. So I'll click the close button on the Format Trendline task pane to get rid of it. And we can see the trendline. So you see that there is a fairly obvious vertical trend. The farther someone travels, the more they are likely to spend. And you can use the trendline to estimate how much a particular customer would spend based on a trip of a given length. So if a customer drove 120 miles, you can see that it would cross here at approximately, I would call that 160, or so.
One thing to note about trendlines when you are working with data that is not time-based. This trendline is only valid for the values in the range that you have already collected. So if you were to say, how much would someone spend if they drove 200 miles, you can't answer that because the longest trip that you have is about 155. So use trendlines, but be aware of their limitations.
- Distinguish between the mean, median, and mode.
- Describe the relationship between variance and standard deviation.
- Identify a nondirectional hypothesis.
- Point out the difference between COVARIANCE.P and COVARIANCE.S.
- Explain correlation.
- Analyze Bayes’ rule.