Transcript for:
Pivot Tables, Scatter Plots, and Trend Lines

so um today's session like i mentioned here we're gonna go through pivot tables scatter plots and trend lines if we don't finish the scatter plots and trend lines we'll add it to tomorrow's session as well um now there's a lot of you in the session so it might not be possible for me to keep an eye on the chats all the time but i do have doreen who said she will keep an eye on it she will let you guys know if this is question she's gonna escalate to me she will let you guys know about that um so yeah so any content related questions you can ask in the chats while we are busy or if something is unclear uh maybe give it a moment see if i do explain a concept and if i still don't you post your question just to to try and keep a little bit of order um so yeah let's get going with the session so we're going to work on the day trips data so i'm gonna um move over onto another screen so let me just share here so welcome to the session and i hope you're going to find it useful so what i'm going to start with today is just the uh where to find things on click up again so there is a video that goes through this if you haven't seen it yet you can go and watch it nice and calmly but yeah in continuous assessment where you have practicals you will get the information so there's some extra microsoft excel help here just thinking about it someone did post on the discussion board a few days ago ask to change their excel so that it uses decimal points instead of decimal co that kind of information you will find here in additional resources and technical reports so there is already a post like that because it's a question that comes up every single year now on the discussion board it's been nice and active with the practicals i'm really happy about that please feel free to go post any questions you have there inline images work best so you'll see how to do that if you go look at the guidelines as well now prep guide you'll find in here so prep guide all the data used in the examples you can find here the practical guidance here some additional notes on the histogram tool and it's just a few slides that i put up um just to help clarify how the histogram tool works that you can find there so that is your first step in preparation so let me actually just go to the announcement page very quickly um so here is your announcement for week two and you'll see the practical section we actually explain everything so we've explained what will be in each of the sessions i explain which case studies to work through what parts of the prac guide and what the sessions will be about now if we go back to practicals uh let's just see where it is obviously i'm seeing a lot more stuff than you guys are seeing um so video resources these especially if you are very new to um the practicals and to using excel these will be very useful for you so here are the data sets that we use in the example so linda and myself recorded most of these videos not all of them um and i've indicated here which videos use which data sets the one what to expect from the practicals this is one i made last week for you guys which just takes you through everything in detail so here you can go watch these videos which are intros if you feel you're comfortable you don't need to watch them so these are your video resources and then if we go back to practicals you will see here we have practical files and here you will see obviously these are the last years things so you guys can't see them you'll see this practical one instruction so there you can download it it has the due dates um all of the information everything you need there so we are going to work through this today and tomorrow then there's your day trips data set that's your excel file that lovely huge data set and a description document which tells you what's in the data sets it's very important to keep track of that as well now your submission will be in this folder i've given you extra information up here just to say for prac one and two for histograms use the instagram tool and also for box plots some people don't have x box plots in their excel the older versions of excel didn't have it so if you don't have that just go into mindtap and office 365 you can access it there now let's go back here so this is where you find everything so we're gonna start working through the practical just now now if you look at the announcement if you post it for this week you'll see that we've indicated there let me actually just make my screen a little bit bigger i tend to like looking at a smaller uh font size so let me just see if i can make it a little bigger so it's clearer for you guys um i don't like big fonts in general but for you guys i will make it a little bit bigger um now here you'll see we've said that for the practicals you need to go through sections one two point one two point four and two point five of the practice so if we move over to our practical guide you'll see section one is actually just the very basics of excel so you can go through that on your own um the referencing is very useful even if you are if you've used excel before you might not know everything here so just scan through it you can see it's not a lot of pages and a lot of it is actually just graphs oh well images so then section 2.1 is doing cross tabulations and frequency distributions 2.4 is the histogram and 2.5 is scatter plots so the rest of this we'll get to in one of the future practicals and every week we'll tell you what to go and prepare now if we go to the practical itself this is what your practical instructions look like so we've given you a whole bunch of information here just read through it this topic generally stays the same so um always just scan through it to see if there's something extra that's been added then we get to the data so we tell you here for this practical you're using sample data and i'm going to highlight that because that is actually quite important so you'll pick up in the lectures that are coming up in the next few weeks that it's important to know whether we are dealing with sample data or population so here we've already told you something very important we tell you where we got but we've given you guys the link to the original data and please feel free to go have a look at that i've included it so that you can see what data usually looks like when we get it from someone like stats ese and um then we are we don't expect you guys to look uh to work with this coda data but you can see for intersect what it looks like um it's important to read all of this and just make sure you understand what your data is about then we've given some extra information here and we've said only respondents who made at least one day trip in the three months um was included in this data set so if you look at this original data you'll notice that that actually has a lot more information than what we've given you so if you thought 17 517 observations was a lot there was actually more so we took everyone who didn't do day trips out of the data set we weren't interested in that now day trip we've defined what it means and we've said that there are some blank spots so you guys will pick that up very quickly that part of the data you'll you'll have empty bits here and there where someone didn't answer a question and instead that's always a question for us what do we do if we don't have this information so you'll see for different variables you'll not always have the same number of responses for each variable um but that's quite a big question to answer for the purposes of this course when we have a um missing observation we're just going to ignore it we're not going to worry about how to deal with missing observations here so that is a bigger question for a later year of study then in your practical instructions we'll always tell you as well and these are quite good hints we'll tell you what we're trying to achieve with this practical what you should be able to do at the end of this and then also um what tools or what functions you might want to use in this practical so in this case you can see we're doing pivot table tool chart tool histogram tool correlation function and the covariance function and you might remember that i mentioned earlier that we have two covariance functions i've given you covariance.s here so we're going to look at why i'm using that one just now now if we look at the practical again a little bit of housekeeping we tell you what when it's due how many attempts all that so you will be allowed three attempts only the best one will count and you get 60 minutes which sounds like a lot um especially if you think three attempts what i've noticed so far is a lot of people who have already completed this didn't even take three attempts and they got full marks because while you're busy with it i've set this one up so that it will tell you yet you're right or no there's a problem now speaking of which there was a small issue with the last question so question three c had an error with the first two parts of the answer where people got it right but it would tell them okay we've marked this with your previous answer and that's why you only got half the marks um which was really weird because it wasn't supposed to be doing that so i did speak to the numbers team they found the setting in the background of the software that was causing a slight issue i had tried something out and it kept my sitting there even though i told it not to keep it uh so they fixed that error and we've done a remark so everyone who encountered that error would have already gotten their full marks for those questions um no issues at all so if you ever pick up something weird like that let us know we'll definitely look into it and if there's an error on our side or in this case it was on the number side we got it fixed really really quickly now we're gonna start with our case studies so our case study one is about crosstabulation so i hope you enjoyed my crosstab joke at the beginning of the session so we are going to try and replicate this so the whole point of these case studies is for you to go and see whether you can actually do this example so if you can replicate this and they get exactly the same as what we have then it means you understand the tool and you can apply it so here you'll notice i've put a little eye icon here which just tells you whenever you see it you'll um find the section of the prac guide which corresponds to that question so i hope that helps you guys um and we give you some hints and things to go through so we're going to start with this in this question we are telling you what columns to use we're not going to carry on doing that for the entire um for the entire um semester you will sometimes have to figure out which column to look at yourself so that is why that document on clicker let's actually just go back to this document very quickly um this document on click up here if we go to where is it practicals and you go to practical files and there we go data set description this is a really really really important document so it tells you in of the day to see what you will find there and you'll find that some of the i mentioned this in the video that i put up about the practicals as well um you might find something that looks very similar in multiple columns so for instance here we have we have shopping somewhere else as well there's shopping in um and i yeah there's no more shop other than that so here you'll see these are expenses occurred during the drop where this was expenses incurred are related to the trip but before the trip actually happened so when we ask you about um money spent on food and beverages you need to know is it before the trip or is it during the trip because that's going to tell you which condom to work from so that is something to go and um pay attention to so this document should be very very useful for that so just read through it once and just pick up where you might find uh things that look similar for instance here as well participants number of people on the trip household is the number of household members who went on the trip so you might have multiple people on the trip from different households but you went to know for this respondent how many of those people were from their household um i'm seeing a question in the chats about where to find the um instructions i'll get back to that just now i see it has been answered um an issue that i saw come up on the discussion board as well is sometimes when you try and download that document it asks you for a password and a username what i think is happening there is that click up is maybe partially signing you out but it doesn't look like you're signed out yet i have encountered that same issue myself i'm not sure why that happened so i would suggest that you guys report that um to the click up support people and ask them maybe send them a screenshot of that my suggestion would be to sign out of click up completely close your browser reopen it sign back into click up and see if the file will download then because it's not supposed to be asking you for a password it usually does that if you're not signed into click up anymore if that doesn't work try different browser in my experience that has helped in the past if that's still an issue please um report it to click up um because it's not supposed to ask you for those things um it's not a password protected file okay so um let's get back to this so let's see the data set so here's my data set i have made it a little bit bigger you can see quite a lot of different columns here lots of information you can see those missing values here already uh where we don't have full responses from people you can all think if you do a survey sometimes you just don't want to answer all the questions or you skip a question because you don't know how to answer it so that is what we are dealing with here as well now if we go back what i'm going to do is i'm going to just go see what columns i need to use so here i need to work with a respondent type of dwelling and whether or not they have access to a home or a landline phone so column j and column u now dwelling is in column j let's just go look at that first it's called welling column u if we go to column u you can see there it is it's called homophone so i'm going to show you guys tricks and ways of doing these things quite easily now what you could do you could copy this over this data into a new sheet and work from that i don't want to do that what i'm going to do is i'm going to work directly from this and when i set up my pivot tables i usually do it in this way because this way i know i'm not missing out on anything so what i usually do is i just click in one of these cells on one of the headings and i go insert my pivot table now we already saw that we have data up to column cr and if i go down here it should be up to row 17 518. now when i want to insert my pivot table i normally just click on one cell and the reason for that is if i highlight like this and i then say insert pivot table it's only going to work on this data and then i have to go and reselect everything so if you click on one cell it makes it a little bit easier so i'm going to click on insert and then up the top here i have an option for pivot table so when i do that you can immediately see and that's unfortunately i can't zoom in on um a click up doesn't or excel doesn't allow me to zoom in on this but on this pivot table it has already selected everything in the day trips 2017 sheet from cell a1 up to cell cr17518 and you can actually see this dotted line that's going around my selection it selected everything in my sheet so it means that when i do a pivot table it everything in this data set um as possible options to include in in my pivot table now what i also like to do practice is to not work in the data set in that original data sheet because you don't want to accidentally change something in your data set and then you get the wrong results so what i do with a pivot table especially is i always go and put my pivot table in a new worksheet um so that's those are the default options anyway so i'm going to click on ok now if we go back to our instructions for what we want to replicate here then we can see that we need our type of dwelling in the rows and i want to put home phone in the column so that's going to be my next step so type of dwelling there it is i'm going to just click on it and i'm dragging it and dropping it into the rows let's actually just zoom in here as well let's see we're not zoomed in at this point okay so i have um dragged and dropped that there now i want my home phone in the column so i can go search through all of these things but especially if we have so many variables it gets quite tedious so this is a trick that someone showed me one day and i was kicking myself because i hadn't figured it out myself so i'm going to share it with you guys you can search here and we know what the variable is called so i just started typing home here and you can see the two options i have is home phone and home theater so i want to use home phone so i'm going to click on it and just drag it over to columns and drop it in there and what you'll see is it has now added the uh responses that we have for home phone up there so i'm going to just make this a bit smaller so we're not going to see the entire thing uh everything that's written here in a moment it's going to disappear a little bit um so we have our headings now and this looks pretty much like what we had in our practical so i'm just gonna close the prac guide so that it's easier to get to the practical questions okay so we have all of this information up we just need the numbers in here now for this i can drag either home phone or the running type into my values box doesn't matter which one i should get to the same table in the end so i'm going to just take a home phone because i already have that um and i drag and drop it in here and you can see it's adjusted the column width i'm going to make it like that so that we can see again what's happening now you can see this isn't exactly what i have over here if you look at this worksheet and i'm gonna just maybe change it a little bit and zoom in so that we can see a little bit better hopefully you can see here the data i have here looks very similar i have everything up to blank everything is in the same order but in my columns i want to have yes first then no and then blank so that's how i've set it up in that first screenshot for you guys okay so easy enough to fix this we can see if you do a quick check you can see that all of the values here on the side correspond to what i have here as well so we know everything is fine we're using the right data everything is looking good so far okay so first thing i want to do is i want to move my yes to the left so yes first they know then blank so all i do to do that click on yes right click on it and we just say yeah move and i'm going to move it to the beginning i could move up as well because it's just one space it will do pretty much exactly the same so i'm going to just say move to beginning and it decides i want to see everything so i'm just ignoring that now i'm just dragging it so you guys can see a little bit better okay so now it's yes no blank so everything is matching so that is my first step now what else is different let's quickly go see so you'll see here here on the pdf i've written home phone because i don't want to call it column labels it doesn't make sense to call it that so to change this it's a little bit tricky when you're working with the pivot tables if you double click on this to change it and you try and type sometimes it doesn't work let's see if it does oh this time it worked if it doesn't work all you do is you click on the cell and up here in this bar you go and change it so that's our formula bar you can go and type there as well if it won't let you do it directly in the cell sometimes that shortcut works and i'm going to go do that with this one i'm going to call it dwelling ipf okay here we go so i have my headings now if we go to um let's just go see just minimize that by accident else looks the same now but in my instructions i told you that i want to have it looking like this so if you look at the difference between these two here i have blanks included in the rows and the columns and i said i'm not interested in missing values i don't want them included you can see there's 43 so 235 blanks where people didn't respond to a question i want to get rid of those um so everything else i want included and this is a tip for your practical that you need to go and complete on um on click up which is actually set up in numbers but you don't have to worry about that so have a look at the crosstab that i give you i promise you on every single one i have filtered things out so you want to make sure that you have filtered everything out that you don't want and that you have kept everything that you do want now if we go back to excel we want to get rid of the blanks so here on home phone i'm going to click on the drop down list there which used to say column labels now i've made it home phone and i'm just going to untick the blanks click on ok and then i'm going to do the same thing here on the drop down list for the dwelling type which used to say row labels it now says dwelling type and i'm just going to untick that and press ok and again i'm just going to drag so that we can see this a little bit better so at the moment you can see there's nothing here that's why we can still see this writing overlapping here not something to worry about okay so now we have this we can go and compare this to what we had here quick way of doing it go check the total so i have 17065 17065 so i know i've gotten to the right um end product so that is pretty much it for lacrosse tabs so now i can of course go ask you guys questions so this is where my next trick is gonna come in i can go ask you guys to answer questions based on this so i could ask you what percentage of uh people have uh live in a caravan tent and don't have a home phone so in other words i would want to go and calculate this as 16 divided by 17 065 um and multiply by 100. so let's write that out so i'm asking you what percentage of people live in a caravan or tent let's actually just do it like this so it's matches and i'm going to make that a capital letter don't have a home phone okay so if we wanted to go and calculate that what we would do is we would go and find the intersection bit so that's where both of those things are true and i would divide it by my total so usually i wouldn't want to go and type those but i want you guys to see the calculation in principle so there we have our answer what i'm gonna do is i'll just copy that over and i'm gonna show you guys another trick now i'm going to show you how to show what uh formula i've entered so i'm just going to put an apostrophe before the equal sign and that's going to tell excel that i actually don't want to see the answer i just want to see the calculation and that's how if you look at the post by jared this morning that i replied to this is how i showed all my formulas by just adding the apostrophe so that you could see exactly what was happening there now this is my percentage of people who live in a caravan or tent and don't have a home vote i could have done this calculation as well obviously we need to just remember to add a percentage sign could have done it by clicking here saying i want to divide by that and i want to then multiply by a hundred and it looks terrible because it's saying get stuff from a pivot table and all of that but you can see i get the same answer now i'm going to show you guys the shortcuts and this is what makes pivot tables so useful so if you right click here on these values you'll see there's a few options here so the first thing and this is something i haven't mentioned before we wanted frequencies in here so when we summarize values we need to summarize by count both the home phone um home phone the responses would have been yes and no dwelling type it would have been what type of dwelling a person is so all of these are qualitative variables and when i drag a qualitative variable and it automatically goes to count dragged a numerical variable into that values box over here it very often changes it to sum by default you have to know to go if you want frequencies go change it to you can see we can also go count calculate averages maximum minimum and a whole bunch of other stuff we're not going to work on that now the other useful thing here is show values as so the show values as you can go choose to do calculations on these values yes there is a question can you guys hear me still i see someone mentioned that i have microphone problems it might just be my connection that dipped slightly so i hope everything um still made sense seeing you here you can proceed awesome okay so now this is the useful trick so here you have three options that will become your best friends so if you say yeah show values as percentage of grand total it will take each of these values divide them by this grand total and convert it to a percentage so do exactly what we've done here so if i go and click on this it has now again we need to just scroll let's actually just make this a different color so that we know this is the one we're interested in and what i'm going to do is i'm going to increase the number of decimal places and this is a nice way of doing your rounding please use this when you do rounding so you don't accidentally round wrong because you round wrong you're going to lose marks now i'm going to increase my decimals and if it does weird stuff like this it just means there's not enough space so all i'm going to do is i'm going to double click on this line here between the c and d you can see what my mouse cursor is doing it's a black line with arrows in two directions once i have that i double click and that actually automatically adjusts the column to the right width i hope that makes sense yes doreen yo mike is still off durian hi doreen i can hear you very vaguely oh i'm so sorry um let me just type it out for you okay okay okay so someone i see is asking about the rounding we will tell you how many spaces to round off to um so increasing decimals uh while doreen is typing the question for me um you'll see here in the number bit so if you go to home and you go here to the number bit these two buttons here the one is going to be for increasing decimals the other one is going to be for decreasing decimals so you can see here that this answer that i calculated by hand down here actually is exactly the same as what excel can calculate for me as well okay so yeah excel is pretty cool but it is it's complicated in the beginning but you guys will get the hang of it very quickly so don't stress about it play with it do different things see what it does and and you'll um figure out very quickly how to use it um so changing to the same thing i'm gonna pay a little bit more attention to that as well um i'm just waiting for doreen's question to come in um so doreen i think when you type the question just tag me in the question because then i'll i'll hear when it comes in as well um okay so if you don't have show values as options do you have are you working on a mac by any chance yes macs are a little different so i think if you um go look at the video resources if you scroll down quite a bit um you'll see there's a specific video aimed at max for the descriptive statistics tool which we need in the next practical um there is also i i remember we found a way of doing it so maybe um just post on if i can ask you to just maybe post a screenshot of what you do have on the discussion board then i'll go and help you i'll go and just try and remember how to do this on a mac but there is a way of doing it i just can't remember off the top of my head now um yeah so boys giving you an option there as well let me just see if anna uh oh sorry if if doreen has managed to type that question firstly um okay so i have gotten the rings message okay so i'm gonna repeat that very quickly i'm gonna just change this back and then i will repeat how to do this bit i'm going to put it as no calculation okay so this is what it originally looked like so if you're on the pc all you do right click on this and say show values as percentage of grand total so that will show each of the values inside the table as a percentage of this and there is actually quite a nice application when it comes to probabilities where you can do something similar we'll get to that later on um so um let's see uh what else there is okay so we're gonna do that show values as percentage of grand total and there it's done that and then if you see doesn't have enough decimal places all you do is you highlight all of it and you say increase decimal and you can do that to as many decimals as you need you can see i can now drag this or i can just do my little trick that i showed you guys um where you go in between the two columns double click and it will adjust the column to the exact right width which i like doing it's much faster than dragging and much more accurate so then we would have gotten the exact same answers we got before now let's say we asked you to calculate um the percentage of people who live in a caravan slash tint and we know it is given not gonna phrase it like we do on click up but you'll see that it's similar on clicker given they don't have a home phone okay so let's say that's our next question so i'm gonna just change this again so that there's no calculation on this so that we can think about the process here so it's given that they don't have a home phone so we are dealing with only this column we know the person we are observing is in this column and we want to know what percentage of the people who don't have a home phone live in a caravan or 10. so in this case how we would do the calculation is we would say 16 divided by 15146 times 100 that gives us our percentage i'm gonna just show you the calculation again just here with my little click there we go and then i'm going to show you the shortcut so let's say we wanted to calculate this with excel again show values as and you can see there's percentage of column total percentage of rows factor so i'm going to say um percentage of column total so it's going to take each of these values divided by the column total and then multiply by 100 and we should get to the same onset in that yellow block so i'm going to say they show values as percentage of column total and you can see if we go and increase the number of decimals and i'm just going to do that we get to exactly the same answer so instead of having to do all the calculations by hand if you understand how the calculation need to be done you can use these tricks in excel to go and do the calculations for you so then you don't have to worry about making silly typing errors okay so why is the tab green at the top and not blue i'm not sure which tab you are referring to it might just be um a um sitting a color sitting on my computer uh which is different from yours okay so the increasing and decreasing decimals um it's just important to know how to do it and this was actually quite a nice example let's say i asked you guys for this answer to six decimal places now if you look at this and you round off you would change that 8 there to a nine but actually that five over there was already a rounded off value so if i increase this decimal one more if i had used the uh um more decimals or if i had more decimals visible i would round this to six decimal places my last decimal would be an eight as it should be if you had worked from this you might have made it a nine so if we round off in excel excel does it correctly it runs correctly so make sure that you use excel when appropriate okay so those are the important things to know for the um practicals for the pivot tables we will use pivot tables quite a lot still there are ways of grouping things as well there's a question on grouping on click up as morning on the discussion board um and we'll get to more of the grouping questions later on so um the example that i've just done now went into a little bit more detail than we have available here but pretty much we've replicated this and i've said that that you will be able should be able to answer questions on clicker based on the results of your crosstab so um there we go that's what we've done here so any last questions or anything from my colleagues that they feel i need to look at again just before we move on to the scatter plots so if you that's a good question about increasing the decimal if you look at what i've just done here you can see i've only done it here if i click on the cell and i increase it only changes that one if i highlight all of this and i then click on these buttons then it will do it for all of them so whatever you've highlighted is what it will increase or decrease the decimal for okay so you guys have now learned how to do crosstabs which is just basically pivot table while using the pivot table tool in excel that's what it's called yeah so you should be able to set these up and then you should be able to answer questions based on these so those are the important things as part of your practical um i i'm seeing some questions on how do you get the percentages on a mac um i've asked someone to just post on the discussion board i can't remember how to do it but i can't go figure it out for you guys i remember we i did figure it out last year with the student we we figured it out and they sent me some screenshots so maybe just post a screenshot of what options you do have available and then i'll help you on the discussion board with that okay now yeah value field setting i think you're right andre um i remember something about that okay so right clicking on the mac macs are i've never worked on a mac so i will do my best to help you guys uh but you can definitely help each other on the discussion board with the mac questions as well i know there's quite a few of you um that do have macs as well okay um so let's move on to the scatter plots i see zara you have a question um not sure if you've typed it um but maybe just type it because uh you guys don't have access to microphones uh when there's so many people in here um and then we can get to your question hopefully quickly as well um okay so the mata the calculations that we were working with those are theory questions so i would suggest um there should be examples of that in your textbook but maybe also just go post that on the discussion board and we can maybe look at um one of those questions on the discussion board you'll see some examples of these questions on the um practical video that i've posted as well okay um so i just want to see this last question here when do the percentage thing when we do the percentage thing does it change it in the original columns or does it put it in new columns um what if you need the numerical values for other calculations so you can always go back to saying no um calculations so you can always go back to your original values and what i find quite useful this is a trick i haven't shown you guys yet let's just go back here let's say you want to still see what you've done and everything i'm going to highlight the whole table copy it so i'm using my keyboard shortcuts you can use the ones up here as well and i'm going to do paste it down here okay so let's actually just move that so it corresponds to the actual question okay so now in this one i can go and do my calculations let's say i wanted to do a percentage of grand total um and you will see that calculation was done in the copied one but not in the other one so you can always do something like that as well okay anything from my colleagues that you think i need to look at before i move on i'm guessing silence means all is well okay so the questions are not part of the preparation but i'm showing you what to prepare so you but you can understand the questions we'll also do things like this in class where we look at different questions and how we would interpret them so you have to always just in these questions think giving you some information is something known what are the unknowns things like that so this will also come up quite a lot when we do probabilities later in the semester so that you can always think of this as prep for the probabilities now the next question let's move on to the scatter plots and of course any questions you guys on pivot tables feel free to post them on the discussion board um we will help you there and it does really help if you thoughts i've shown you guys now how to show formulas if you want to show multiple formulas what i've done in in the late class now as well this one looking at 2.5 of your practice for the scatter plot here we want to investigate a possible relationship between the amount of money spent on land transport we've told you here which um column and on shopping during the day trip so both of these are during the day trip and if you think about um the documents i showed you earlier with the key um you will see that this before and during both of these have to be during so you have to make sure you use the right column so we're going to use column b q and bw for this one now for this one i'm going to actually create a new sheet and i'm going to copy um these two columns over into exon then i want to show you something very important here as well so we need bq bq and bw so what i'm going to do is i'm going to highlight bq then i'm going to hold the control button on my cal on my sorry on my computer in not for my calculator and uh let's just first get there so i'm good i've highlighted the whole of eq by just clicking on bq so i do not want to drag down 15 000 rows or 17 000 rows to highlight everything by clicking on bq i get everything in that column automatically then i press and hold the control button on my keyboard and i'm going to do the exact same thing here on bw i'm going to click on bw so i have now highlighted bq and bw then i'm going to copy it i'm doing it with my keyboard shortcut which is ctrl c it's faster if you know the keyboard shortcuts but you can also just click on the copy button up here and i'm going to go put this into a new sheet because i do not want to try with the scatter plot moving left and right and all over the place it gets very confusing so i've moved this over into a new sheet my original data is there it stays the way it was i'm not going to edit it but i have everything i need over here now if i want to do a scatter plot first thing i'm going to do i'm going to highlight both of these new columns then i'm going to insert and i'm going to click here you can see there's a picture of a scatter plot so i'm going to click on that and i'm going to do the one with where the dots are not connected so remember we want to fit a trend line which is not the same as connecting all the dots so i'm going to choose the option the first option which just gives us the dots okay so if i click on that you can see it is giving me a plot now the first thing i want to do is i want to go check if this is the right cut so let's do that and immediately we can see something is not right here here i have three dots in the picture that i'm trying to replicate and then one on the side here on this one it seems like my axes have been switched so what excel does is the first one and we can figure this out quite easily if you don't know which one is which one because obviously we don't know yet let's try and figure it out we can see here the maximum value on this axis is close to 70 000 maximum on this one is close to 12 000 so what i'm gonna do is i'm going to use my max function to figure out what my max land transport value is and i'm going to drag this function across because when i do that it also shifts the range that i've selected so again when i um input into this function i'm selecting the entire column and saying do this calculation on this entire column just so that i make sure that i don't miss something and you can see it's much faster than trying to drag the actual range like from the beginning to the end that's just no one has time for that so we've calculated our two maximums so based on this i can see that at the moment on shopping is here on my vertical axis on lan transport is on my horizontal axis but if i look at my instructions here it says shopping should be on the horizontal axis and the other one should be on the vertical axis so before we get cut off because i see we are going to get cut off this calculating the maximum was just my way of figuring out which one was on which axis so excel automatically puts the first one on the horizontal axis the second one on the vertical axis so easy way of fixing this i'm gonna highlight this copy it and insert it over there gonna delete my graph and then i'm going to highlight it like this so it doesn't matter which order you click it actually matters physically which one is first and which one is second excel is weird so i've highlighted my two columns now and if i say insert scatter and i click on that then my graph looks correct okay so i fixed that issue now for homework we will carry on with this example tomorrow so for homework i want you guys to go and check and i've given you a hint at the beginning of the session for this um i want you to go and replicate this try and fit the trend line all of that to the graph and i want you to go check that you can get the exact answers that i've given you here if your covariance is off by a few decimals or something you've used the wrong function and i want you to think about why that is then also histograms i want you guys to go and try and replicate this so we'll look at this in tomorrow's class as well so any other questions you have that we we still need to look at you are welcome to go and add them onto the discussion board like i said with screenshots in line please go look at the discussion board guidelines they will tell you how to do the inline posts um and yeah then we can carry on with this tomorrow so any other questions you have you can also add there and i can see if there's a question that seems quite important we can also deal with the tomorrow in the session so i will go through the chats after this as well and see if there's anything else that i pick up that we can just come back to tomorrow so i hope you guys have a lovely day i am going to stop recording but i'll still be here for a moment until we um uh do the um well until we get kicked out so your homework just as a repeat is that you need to just go finish this case study case study two and try case study three before tomorrow session and see if you have any questions feel free to post them on the discussion board