 Statistics and Excel. Wages, data, box plot or box and whiskers. Got data? Let's get stuck into it with statistics and Excel. You're not required to but if you have access to this online one note we're in the icon left hand side the one note and Excel presentations 10-20 wage box plot tab. We will also try to upload our transcripts so that if you choose to you could go to the view tab up top use the immersive reader tool you can change the language here if you so choose and either read or listen to it in multiple languages. One note desktop version here looking at our information our data on the left hand side which we are imagining to be salary income information possibly related to employees of a corporation or a business or possibly related to faculty at a school if you want to imagine it that way so note that usually when we first get the data it's not going to be organized in a fashion that's going to be very useful to us so for example if we did a random sample if we asked people what their salary is if they were willing to give us what the actual salary was then we might order the information by the people that we randomly asked what their salary was or we might get the information if we have access to these salaries by alphabetical order we might have a list of our employees in alphabetical order and the salaries related to them clearly when we just look at the data set in that way we might be able to extract some information from it but it becomes quite difficult to do so because there's no ordering of the data it's going to be difficult to get meaning from that data and clearly if you're in a situation with salaries the common situation would be that you're trying to argue that you should have more salary you should have a larger income level so you might look up data if you had access to the actual salary level in your particular organization or you might look up salary related to people in your profession and say hey look this is the comparative salary this is what I'm making and so on and so forth but clearly if you were to present something in this order it wouldn't be that useful right if you had a list of people salaries and and you presented in a meeting and tried to argue that you should get a raise because of this list of salaries then this wouldn't be the most efficient way to present that list if it was an alphabetical order or something like that what you would want to do typically is organize this and the first way to organize often will be from lowest to highest or highest to lowest so in Excel we will do this by basically making a table we have a nice sort field so we can do this sorting very easily at one time it was not so easy it's great that we can easily sort this information now so now we've got it from highest to lowest which is probably what we're going to be looking for we're gonna be saying hey look this person's making 84,000 I'm not you know whatever and so on and so forth and we can get a decent idea of the range by simply looking at it in order now that's clear when you look at this kind of data but if you are looking for a huge set of data that has a lot more information in it then this would still be fairly difficult to look at and extract a lot of meaning from it so the next thing to do is usually to make some kind of pictorial representation of the data so the pictorial representations very important they're not simply just a crutch type of tool to explain stuff to the layman we want to have the pictorial type of data now the the box and whiskers or box plot is a fairly easy kind of thing to put together and I think one of the attractiveness of the box plot versus what we'll take a look at later which will be a histogram another way to sort the data possibly one that you'll see more commonly because it gives you another sense or a different sense of the spread of the data but I think the box plot was kind of easier to draw if you had to draw something by hand you know it might be a little easier to draw the box and whiskers than the histogram but now of course we have Excel so we can fairly easily draw whatever we want to draw so we can make the box and whiskers if we so choose and we can make the histograms fairly easily if we so choose and we can make bar charts which we will practice in Excel so if we were to plot this data we could we could see what we have here we've got the wages and we have to come up with what we want to be the intervals of the wages so it starts at 50 55 60 65 70 75 80 85 and then we've got our information on the box and whiskers for the pictorial representation so we actually put the data in here as well which you can do in Excel so now let's kind of analyze each of these points of the box plot that's going to be our next kind of component so I'm going to make this a little bit smaller here so here's our same box plot on the right and let's look at the meaning of some of these numbers in the box plot so usually we want and we have the average or mean that's one of the first types of calculations that we will typically do so if I think about the average how would we calculate the average of this set of numbers now if you had to do this old school with a calculator it would be quite tedious but the concept is fairly straightforward we're going to add up all of these numbers boom boom boom boom boom boom and then we're going to divide it by the total number of numbers that we have we'll count the numbers and divide by the total so if I if I was to do that the manual calculation I can add up all of the numbers and then divide it out so I can think about that with two functions the sum function which would add up all the numbers and then divide represented by the slash here and then this count function actually simply counts the numbers so if you're interested in that in Excel we'll get to it you can take a look at a problem in Excel and then if I wanted to use and just a function the function is the average function so in Excel I can just use the average function and then select this entire column of numbers it'll give me the average remember in the average is the mean same thing two words same concept so that's going to be how we can calculate the average that's represented over here with the X now note that the X is not exactly the same thing as the line the the X is the is the average the line represents the median the middle point of the number so let's look at the next one the minimum not included not including outliers so the minimum number not including the outlier