Excel Statistical Analysis 01: Data & Statistics

welcome to excel business statistical analysis video number one where we talk about data and statistics now this is a highline college business 210 statistics class and we're going to be using microsoft 365 excel now this version is the best version in history however most of the calculations and reporting and charting that we do in this class can be done in any version of excel now here are all the topics for chapter one here's the topics for video number one we'll cover the folder and files for this class then we'll talk about data analysis data and tables and then we'll cover what is statistics now we're going to start with folders and files and down here this is called the task bar and if you hover over that folder icon you can see this is called file explorer let's click to open now on the left you get to select the location where you're going to create folders and save files if this is your computer you get to select whatever location you want if you've checked out a computer from highline then you need to plug in your usb drive and select that location i'm going to use the desktop and then over here i want to create a folder but before we do that i want to go to the view tab and notice that looks like an excel file and this looks like a word file but i don't see the extension and for this class you're going to need to come up and check file name extensions and when i check this now you can see the dot file extension we can have excel file extension such as dot xlsx dot xlsm dot xls and a bunch of others the one we're going to use in this class is dot xlsm and here this is a word document so you see dot docx now i want to create a folder here you can go to the home tab and use new folder or you can use the keyboard which shows up in that screen tip and i'm going to use that keyboard control shift n and we want to name it and you're going to put your name business 210 excel statistics now where it says your name you're going to put your name not my name your name and then enter now over here on the left i can select this folder and i'm looking inside and there's nothing there but later you're going to have lots of files and all the files you use while you're watching the videos and doing your homework problems you're going to save those because later you can use those to look things up when you complete your tests now let's go to our people website there it is this is not canvas this is our people website this is where we get all of our files and click on the links to watch the videos now i'm just building this class right now so the only link there is is for chapter one i'm going to click on this up at the top that one excel file right there is the file you're going to use while you watch the videos so you'll open it and then click on the link and watch the videos and follow along now this file here is for a homework problem so after you watch all the videos then you jump over to the textbook read the textbook do the homework problems all right so i want to download this excel file right click save link as i'm using the save as dialog box just like windows explorer i'm going to select desktop select my folder once i have it selected on the left we can use this file name there's the file extension then we click save i am not going to click this to open it i want to go back to windows explorer so i'm going to click this icon selecting whatever the location on the left is now i'm going to look at the file and open it later when you have lots of files you can come back to any one of these files and simply double click to open it now when you open the excel file this worksheet tab is showing now next video is when i'll introduce excel and how to navigate through excel all we're going to do in this video is we're going to use excel to click on sheet tabs and look at some definitions of terms we're going to use in this class so i want to take my cursor and click on the data and table sheet tab now before we even define what statistics is we actually have to define the broader category data analysis and the definition is convert raw data into useful information to gain insight and make decisions if we click on the sheet tab what excel does here's a simple example here's some raw data it's a bunch of revenue numbers and if we need to take all those numbers and convert it to an average that we can use in a budget that's an example of data analysis it's also an example of statistics we can also take this data set with a bunch of revenue numbers create a tabular display or a graphical display here we've created an insight about customer behavior we can from this chart say that 86.5 of the customers spent less than 125 bucks all of this is data analysis and statistical analysis now back over on data and table but here's the thing data analysis has many synonyms data analytics analytics business intelligence statistical analysis mathematical analysis and of course data analysis now this definition right here holds for all of these if we just add statistical techniques to the beginning of this then we have the definition of statistical analysis if we add using mathematical techniques then we define this if we say using business data then we're talking about business intelligence if we say using statistical mathematical or just data analysis techniques then we're defining data analytics or analytics so when you're out there working you can hear any of these and they all mean the same thing take raw data convert it into useful information to help make decisions now before we can move forward we have to define things like what is data what is a table this is an example of not raw data the bits of data are separated by a comma and stored in a single cell now although it's common to get data like this before we can analyze it we'd have to break it apart into individual cells down here this is raw data on this particular day for the publicly traded stock google this was the closing price for stock and this was the volume for that day of buying and selling stock so this is the raw data but what are these we need to scroll down here you can use the scroll bar over here or the wheel on your mouse you can also use the scroll arrow what's a proper data set sometimes called a data set sometimes called a table a table is made up of fields those are the columns in the table here's the date field close price volume and company field name that's the name at the top of the field that describes what goes into the field date means you're only putting date in this column close price goes in this column volume and company now what's so important about field names is that all the data we're going to get is going to be in a field and the tools that we use like a pivot table power query sorting filtering they all communicate with this data set based on field names and a record that's one row of related data in a table this is one record so on the 25th google their stock closed at 2775 and had a volume of 1 million 54 000. now the table requirements in excel the first two of them are universal for all tables of data that are stored anywhere but in excel there's a third one the first is field names must be in the first row the second one is all subsequent rows are related data stored as a record and the third one is that empty cells or excel column row headers must be all the way around the table so these are excel row headers but notice there's empty cells and i even have a little because i didn't want this data to touch it there's a little teeny completely empty column here if you put a rogue piece of data like a note right here next to your table in excel it becomes part of the table and it makes using the tools like pivot tables power query sorting filtering and so on difficult to use so no notes next to your data sets now next i want to zoom out if you look down here this is called the status bar and right there you can slide this right now it's at 190 so we want to scroll it down to a smaller percentage now you can use the scroll bar right here or you can hold control and roll your wheel on the mouse so i'm going to hold ctrl and roll my wheel and i'm going to scroll down just a bit now we already defined raw data which is the bits of data stored in its smallest form the textbook defines data as facts and figures collected measured summarized and analyzed for presentation and interpretation the facts and figures collected that's the raw data in a table when we measure and summarize that means we make some calculation like the p e ratio for google or down here we went from this raw data got a unique list of company names and calculated the mean population standard deviation and sum volume each of these is a calculation or a summary or a measurement and then from the facts figures that are measured and summarized we can analyze and then present and interpret for example down here we have a tabular presentation and we can use the p e ratio price to earning ratio for a particular stock to interpret it and make a decision whether we want to buy the stock or not buy the stock now a table is defined as field names in the first row records and subsequent rows now this is a table of raw data this is a particular type of table where there are elements in the first column and elements are a unique list of entities on which variable data are collected now each company name is an element in this element field a variable is defined as a characteristic or attribute of interest for the element there are five variable fields in this table ticker year incorporated industry number of employees and p e ratio now there are six fields in this table one element field five variable fields and for each record there are six cells one element cell five variable cells an observation is defined as the variable data for one element so here's the observation for google and the book defines measurements as measurements are made for each variable to provide data in the observation for example loosely speaking we went out and looked up the ticker year incorporated in industry and measurements as we typically think of them counted the number of employees calculated p e ratio and down here we made mean standard deviation and adding measurements now we want to go over to the sheet data terms now we need to talk about the different types of data so we understand what calculations could be done on certain types of data and what presentations can be done on certain types of data broadly speaking there's two types of data categorical data means data grouped by a category and with this type of data you either have nominal or ordinal data now nominal data has a category but there is no rank if we're looking at phones iphone samsung or eye color brown or hazel there's no rank to that data the calculations that we can make on nominal data are counting that's it once we get the counts then we can calculate percent of total based on those counts so from our phone column we have two possibilities iphone and samsung we get a count of three for iphone one for samsung if we calculate the percentages three compared to a total of four is 75 percent and for samsung it's a count of one and a percentage of 25 percent that's all we can do with nominal data now when we jump over to ordinal data we have a category but now we have a rank but we don't know the distance between each rank if we look at rating of a teacher bad good and great that's definitely a rank but we don't know the distance between bad and good and good and great the calculations we can make on ordinal data are counting percent of total based on those counts and if we have a number that represents the ranking category like for rating of a teacher then we can calculate an average if we add all of these up we get a total of eight divide by 4 the average rank would be 2. now quantitative data just means you have a number and there's two types interval and ratio with interval we have a rank we know the distance between each rank and zero is either not in the scale like iq scores or sat scores which go from 400 to 1 200 or 0 is just a point on the scale like celsius or fahrenheit temperature the calculations you can make are you can count calculate percent of total based on the count averages differences but ratios are not okay and the reason is because of the number zero here zero isn't even in the scale and for fahrenheit temperature it's just a point on the scale if you take 20 degrees and divided by 10 degrees and get 2 you cannot say that 20 degrees is twice as much as 10 degrees similarly over here you cannot take the score 1180 and compare it with division to 590 and say this student did twice as good as the student with 590 points however if we jump over to ratio level data we can rank this data and we know the distance between each rank and this level of data involves scales where zero means nothing exists when you have no money that means no money exists kelvin temperature that actually does start at zero and it means no molecular kinetic energy and of course for a score on a final that goes from 0 to 100 0 means nothing exists and of course now we can do all these calculations including a ratio using division we can definitely say 12 000 is twice as much as six thousand and going this direction we can say hey moving from six thousand to twelve thousand that's a hundred percent increase all right so categorical data we have nominal and ordinal quantitative data we have interval and ratio and knowing the differences between these will help us figure out the right calculation to make for the given type of data now if we scroll down and over and zoom in a bit there's a few more terms for data now when we're talking about numbers we have discrete numbers and those are counting numbers and have gaps between each successive number so for example one 2 3 or 1.2 1.3 and so on discrete data answers the question how many continuous numbers can occupy any value over a continuous range and depend on the measuring instrument like time weight temperature money now dollars don't seem continuous because we have pennies but many statisticians treat it as such when you have a continuous variable it answers the question how much now knowing the difference here will help us with things like what type of column chart to create for our data there's also cross-sectional data and time series data cross-sectional data is data collected at the same or nearly the same point in time this data set for our companies contains cross-sectional data on the other hand time series data is data collected over time this time series data collected for this excel line chart shows amazon's stock price over a five day period now we next want to go talk about the definition of statistics so i'm going to click on the statistics sheet tab now the definition of statistics well there's number facts like the us unemployment rate reported in january 2021 was 6.3 percent sue rad kulinader a student ranked at the 90th percentile for the test third quarter youtube advertising revenue was 7.2 billion versus 7.4 billion expected 6.3 90th percentile 7.2 and 7.4 billion those are all numbers that are statistics but the subject of statistics defined in this textbook statistics is the art and science of collecting analyzing presenting and interpreting data now wait a second art science how do those work together well if we go over to the sheet art now the science side of statistics is when we take raw data create useful information to make decisions we're not just going to guess about what the shape of our retail sales data is we built a histogram and here based on evidence we can clearly see most of the sales are for a small dollar amount now when we build a histogram we have to do a bunch of steps and deciding how many classes that's the art side of statistics there's no exact formula for deciding whether i want 6 we can see the histogram change or 12. it takes practice it's a judgment call and that's the art side of statistics so here we have an example of the science and art of statistics working together back over on statistics now we'll do two types of statistics in this class the first one is descriptive statistics that's data that's summarized and presented tabular that means a table of information graphical charts graphs visualizations and numerical some measure or calculation like average mean median and mode if we go over to tgn there's the raw data and down here and for descriptive statistics we have a tabular example a numerical example and a graphical example back over to statistics now inferential statistics is the process of using data obtained from a sample to make estimates and test hypotheses about a characteristic or attribute of a population set in a different way we'll take a sample from a population and draw a reasonable conclusion that can help us estimate the unknown future now population just means the set of all elements of interest in a particular study most of the time populations are too big so as a result we have to take a sample a subset of the population now let's look at an example of inferential statistics over on example of inferential statistics we'll do this example in chapter nine this is a production example where a machine is filling boxes of cereal and we will go out and sample box weight to see if the filling machine is filling correctly now the data is down here and we'll do some techniques to help us infer from this sample something about the population from our sample data if we see a p-value and that's the blue value here that's bigger than the gray area which is the level of significance then it looks like the machine is filling correctly now i'm going to hit the f9 key because i have this data automatically randomizing and we hit the f9 i could still see a sliver of blue there f9 f9 uh oh now from our sample data we can infer that the machine is not filling correctly so we have to go out and adjust the machine so it is filling accurately this is an example of inferring from our sample data something about the population inferential statistics and we'll do a lot of it like chapter six and after all right in this video we defined statistics we looked at various data terms like categorical and quantitative and we talked a lot about raw data data tables and measurements now next video we'll get an introduction to excel all right we'll see you next video you

Channel: ExcelIsFun

Views: 8,475

Keywords: Statistical Analysis, Excel Statistics, Highline College, Business Statistical Analysis, Busn 210, Mike Girvin, excelisfun, Mike excelisfun Girvin, Data Analysis, Analytics, Microsoft 365 Excel, Excel Statistical Analysis, ESA 1, Define Data Analysis, Data Analytics, Categorical Data, Quantitative Data, Nominal Data, Ordinal Data, Interval Data, Ratio Data, Discrete and Continuous Numerical Data, Define Statistics, Descriptive Statistics, Inferential Statistics

Id: xxpTg-pBXjQ

Length: 22min 56sec (1376 seconds)

Published: Mon Nov 08 2021