Excel Statistical Analysis 08: X-Y Scatter Charts for Linear Data: Excel Chart? Analyze Data Button?

welcome to excel business statistical analysis video number eight and in this video we're going to talk about scatter plots to reveal a relationship between two quantitative variables and you are not going to believe i'll show you how to do it manually and then we'll use microsoft artificial intelligence analyze data feature to automatically create these scatter plots now our first example has this data set we've collected for 15 students the hours they spent studying for a final exam and then the final exam test score so each record represents a student here this student studied 16 hours and got a score on the final of 88 but here's the thing if we suspect that there's a relationship between our studied and test scores for example we might suspect that as our study goes up test scores tend to go up or maybe it's as our study goes up test scores tend to go down or maybe as we look through the student records there'll be no pattern or relationship at all well we don't know until we look at the data for some evidence now this is what the scatter chart will look like when we're done with the x variable on the horizontal axis and the y variable on the vertical axis we can see the relationship with this visualization and here it looks like as ours studied increases the test score tends to increase now this type of relationship is called a direct or positive relationship we can get different types of relationships though here's the as hours studied increases test scores tend to increase here's an example where we plotted absences in the class against the final grade so as the absences in the class tend to increase the grade tends to decrease this type of relationship is called indirect or inverse or negative here's an example where we plotted customer age and amount spent and there doesn't seem to be any relationship here's another direct or positive relationship it looks like as the number of ads on the radio during the week increases the car sales tend to increase now when we create a scatter chart we're comparing two quantitative variables to see if there's a relationship we have those two quantitative variables the horizontal axis is called the independent variable or predictor variable or x value the vertical axis is the dependent variable or the predicted variable sometimes known as f of x or y and very importantly we have two numbers that we're plotting one on each axis now i mentioned that because the number one mistake when plotting x and y to create a scatter plot is highlighting the data set insert and instead of scatter people use the line chart a line chart is for one number on the vertical axis xy scatter that's for two variables one on the x one on the y now when we do this it's automatic but as you probably remember from your algebra class if we take this record right here there's the x so you actually count out along the x out to 16 then with the y value of 88 from that point you count up to 88 then you put a marker in an x y scatter chart when you hover over the marker you could see it shows you the x 16 and the y 88 another important thing is that when you set up your data set the x has to be on the left the y on the right if you have the y on the left and x on the right the chart will plot the y on the horizontal and the x on the vertical which is incorrect and also when we plot the x y data we don't have to summarize first like we did with our cross tab we just plot the raw data points and in fact if you have xy data in a pivot table and then try to create a chart the charting engine won't let you do it all right let's see how to create this xy scatter chart on the sheet scp we have a data set here and i want to exclude students so we don't click in a single cell we highlight just the x and y columns go up to insert chart group and if we have raw data and we're trying to see a relationship we use scatter but if we have an x y scatter model built with formulas like a fixed cost variable cost model then we use scatter with smooth lines but we want scatter click now this chart came out of the box as a fixer upper because it's missing important elements for an xy scatter chart and the most important things it's missing is the x and y labels we need to know what both numbers represent so the first thing i'm going to do is go up to the green plus and check axis titles the y-axis is selected with a solid line so i type in equal sign and then click on the label for the y variable and enter i'll do the same thing for x that's already looking much better the chart title i'm going to select it equal sign and have a really long label in cell m8 and enter control shift f because i want to change the font size to 10 and enter now is there a relationship between hours studied and test score that's a good chart title but i also put a description of the relationship and this is a direct or positive relationship because as x increases y increases now that's the visualization for a scatter plot right there that's all we're learning how to do in this chapter later in this class we'll learn how to create formulas to create the regression line correlation and r squared however in an excel scatter chart there's a built-in capability to show the line and the equation if we go up to the green plus we can check trendline if we select the line very carefully and use control 1 it'll open up the task pane we definitely want linear and let's check the box for display equation and display r squared come over and we'll use our move cursor to move it somewhere down below and then with that selected control 1 and i want to change the fill i'll say solid white so i guess i already have it as white and then border solid line and black now later we'll manually calculate all these things so we can use them to create a predictive model but bam there it is from our xy scatter data we created a scatter chart with correct labels and title and even show the regression line the equation and r squared now all of this was done manually which sometimes that's how you have to do it now this sheet was scatter chart positive because we have a positive relationship here let's go over to scatter chart positive two here we have number of ads on the radio during the week and car sales let's click in a single cell because here we only have x and y and in fact i definitely want to change the labels up here this is the x because we're thinking that the more adds we have the higher the car sales will be at least we're hoping that that's why we collected our data and now we want to create an xy scatter to visually see hopefully that the relationship is positive now for this example we're going to go up to the home ribbon tab over to analysis and when we click analyze data internally microsoft's artificial intelligence ai for short looks at the data and tries to find patterns and then create pivot table reports and excel charts so i'm going to click this button it sometimes takes a while and this is obvious to the ai and so right at the top it looks like we have our xy scatter so i'm going to click insert chart and this is absolutely amazing for decades i've been creating xy scatters and manually putting the labels but it got both of these perfect even the title at the top fields it names the x field and this field up here highly correlated now we'll learn how to calculate correlation later and that'll tell us the strength of either the positive or negative relationship but that analysis analyze data button did an amazing job now let's go try scatter chart with a negative relationship at least we're thinking absences in class versus grade in class so i'm going to click in a single cell home analyze button it looks like this is the chart we want notice it creates xy scatter a pivot table a bar chart over here but we want this first one so i'm going to click insert chart now the title didn't come out quite as nice but this is an acceptable title final grade y that's the predicted variable by absences in the class the x variable and it got both of the labels correct it looks like we have a negative relationship here here's another way instead of using the green plus if you come over to one of the markers and right click add trend line it'll add the trend line and open the task pane and then i can click equation and r squared move it over here ctrl 1 format this a bit and that as a visualization of x y scatter data is looking good now let's try one more sc no for there's probably no relationship here home analyze data and this one looks okay but i don't like the chart title down here there's some column charts but watch this this is a great trick i'm going to type using the field names what i want up here and hopefully analyze data will give me a better chart xy scanner amount spent that'll be the y variable by customer age and now when i hit enter that is amazing insert chart and it clearly looks like there is not a relationship in fact if you right click add trendline and check equation and r squared with r square at zero and a horizontal line clearly there's no relationship all right in this video we saw four different data sets with x and y data and we plotted that data on an x y scatter chart and we saw three of them using the analyze data button here we got no relationship here we got a positive relationship between number of ads and sales over here we observed that as absences increase in a class the class grade tends to go down that's a negative or inverse or indirect relationship and we started it off with a direct or positive relationship as our studied increase test score increased also all right we'll see you next video

Channel: ExcelIsFun

Views: 3,182

Keywords:

Id: qRYxIrzo8rA

Length: 12min 27sec (747 seconds)

Published: Sun Dec 12 2021