Computing z-scores in Excel 2016

[Music] hello there this video will show you how to compute z-scores and Microsoft Excel to compare different values within different distributions and within a single distribution this video is primarily going to look at using z-scores to figure out if there's any extreme values in your data set or in other words any value or score that has a z-score less than or equal to negative 2 or greater than or equal to positive 2 all right so we're looking at some data looking at the different types of bread and the different days of the week where we collected data looking at how many loaves of each bread was sold at great harvest bread company so the first thing that we're going to want to do is to figure out the z-scores down here is where I'm gonna put them for each bread type now real quick I noticed when I scroll down I can't see the bread types I don't like it so go to view freeze panes freeze top row now when I scroll down I can still see the different bread types now I want to point out that technically we are going to be creating z-scores for sample means but we aren't really looking at all of these values at the same time and then figuring out different z-scores associated with different means from different sample sizes here what we're gonna do is basically create a mean value for each bread type across all these values and treat those means as single scores or X values basically we're just you going to use the mean as a single score that represents the overall sales of each type of bread then when we're going to do is figure out the mean and standard deviation of all these means so the mean of the means and the standard deviation of the means and then figure out the z-scores all right so let's check this out we're gonna put mean equals and you've done this before and separate lessons here so equals we're gonna highlight all of the harvest white values an easy way to do it click the bottom value scroll up to the top hold down shift click the top value or of course you can just kind of click and drag all the way down hit enter whoa what just happened let's see equals try again average double click the word average that's important then highlight shift to highlight all them all right that's more like it so now we're gonna click get the little plus sign at the bottom right drag over to copy that formula across so now we have the mean or the average for each different bread type and just to check the formula you can see if we look up here this is the average of now the F column F 2 through 22 so that formula was copied over if you're having issues copying the formula over you could always just click and drag starting with the first formula you did go to the Home tab and fill right okay so these are what we're going to treat as X scores these are the scores for each different type of bread now before we can figure out what our Z scores are we have to calculate the mean of these values and the standard deviation of these values so let's go ahead and do that right here so the mean of means we can say equals I'm gonna just double click right here between a and B or that double-sided arrow is so that the mean of means is displayed and I'm just gonna write SD equals 4 standard deviation so to figure out the meaning of all these means we're gonna do equals start typing the word average wait for the word average to pop up and double click it then highlight all of these means and hit enter to figure out the standard deviation equals STD and we're doing sample standard deviation because this is a sample of data it's not all the bread ever sold double click that standard deviation for the sample highlight those mean scores again that we're really just treating as X values for our purposes and hit enter so now we know that the mean of mean bread sales for all the different bread types is about three hundred and sixty-four 0.485 or about three hundred sixty four loaves if we're gonna look at whole numbers and the standard deviation is 191 so if we think about this we can say like the range of most bread sales remember most bread sales are going to be within one standard deviation of the mean so we could put from the low end equals this mean of means minus the standard deviation to equals this mean a means plus this standard deviation so we know that any type of bread that has a mean number of loaves soul between 173 and 556 ish is really just a kind of a common value it's not really unlikely to happen there's a huge variation here across the different types of bread for sure remember this would correspond to a z-score of negative 1 the mean minus one standard deviation and this would correspond to a z-score of positive 1 the mean plus the standard deviation just to confirm that I'm going to show you how to use the z-score formula here before we do it for the actual scores so Z equals so equals type in stand get the standardized double click it highlight this x value the actual value itself comma the mean value comma the standard deviation and check it out z score of negative 1 let's do it for the other one equals standardize let's just start typing in standardized till it pops up double click this x value comma this overall mean value comma and the standard deviation enter and there I just proved my point so now we're ready to compute the z score for all the different bread types so we're gonna go here equals esti double-click standardize click on the actual x-value comma click on the mean be careful here once you click on that hit f4 or put a dollar sign in front of the letter and the number this locks it in so that when we copy that formula across it's going to be reading this mean for every single one and it's not going to look for a mean value down here for each subsequent column we're going to do the same thing with the standard deviation because for all of these we want to use the same mean and standard deviation for the formula so when we click the standard deviation hit f4 or manually type in a dollar sign before and after close it in a parenthesis and hit enter now we can click this drag across with the little plus sign at the bottom right or if that's not working for you click and drag starting with the first one fill right and we have all of our Z scores for all the different types of bread so we can see here and this is because we're looking at the same distribution the conclusions we make with the mean and z are going to be really similar the highest the bread with the most number of loaves sold is harvest white and guess what it has the largest z-score the bread that has the fewest number of loaves sold would have been the sourdough and check it out it has the lowest z-score notice that the value that is above the mean of 360 for all those values our starting values that have or that are below and I'll highlight those the mean of 364 have negative z-scores right and the values that are above the mean of 364 have positive z-scores and if you look at the for instance sourdough we can see that sourdough is the number of loaves sold is one point two three five one six six standard deviations below negative the mean it's just helping you remember how to interpret z-scores so maybe a practical application of this would be let's say that the store at the great harvest bread company the store manager is considering moving any extremely unpopular types of bread from production so maybe any bread type of the z-score ladder below negative two and they also want to raise the price for extremely popular types of bread so any bread type with a z-score of 2 plus well based on these these scores none of the bread types have an extremely low or a high level of demand that actually warrants any changes so they can just keep functioning as normal if they wanted to change their standards maybe get rid of the least popular type of bread they could do that here but they would are here but they want to necessarily needed the z-scores to do that ok here we go moving on to a situation where we are looking at different distributions and we're gonna try to compare performance across different employees while controlling for the location that they actually work at so let's say that the Las Vegas and Henderson great harvest bread company franchise owner wants to increase the sales of Savannah bars so that's the data we're looking at is their sales of Savannah bars so to motivate employees to push the Savannah bars the franchise owners promised a two hundred dollar bonus to the two employees with the most impressive mean number of Savannah bar sales in the next month and promises to suspend the two employees with the least impressive number of Savannah bar sales in the next month now to make sure that this evaluation is fair and not biased by the employee working at a busier or a less busy location the franchise owners are going to look at each employees z-score based on their mean sales compared to the overall mean and standard deviation of sales within their store location so the employees with the two highest z-scores will be given the bonus and the employees with the two lowest z-scores are going to be suspended so let's take a look at the Z scores for each employee within their actual location so again looking at the data savannah bar sales we're going to give a bonus to the two cashiers who have the highest sales within their location across the board suspend the two cashiers who have the least impressive number of sales amongst the rest of the people at their location so you'll see Sally mark Lonnie Kate and Gerry I'll work at the Mark Street location and you'll see all of their scores here you'll also notice that I already did the average or mean and standard deviation for these by just doing the average formula with these scores and the standard deviation formula these scores and you'll see that for all of these so we're gonna figure out what is Sally mark Lonnie kate and jerry's z-score based on how many Savannah bars they sold over the past month per week on average compared to the actual average of the whole store so here we go so equals stat look for standardized double click it highlight their actual score you can't see it but if you go far enough to the left you'll see the little plus sign you can just click it you see it's highlighted if it doesn't let you click it you could always manually type in c6 then comma the mean for the store remember a for it so it locks it in or a dollar sign in front of the letter a number because we're gonna use that mean for all these cashiers it's not gonna change comma standard deviation click that F for it to lock it in hit enter then click on the Z score go down to the bottom right where you see the plus sign or just highlight down and fill down well we don't want this because there's no score there get rid of that now you have the Z scores for all the cashiers now let's do that for each cashier at each location so for the horizon Ridge location we're going to look at this mean and standard deviation to evaluate these cashiers because maybe the tropicana location is just busier and that's why some of these scores are higher right so to control for that and not just automatically give the bonus to the person at the busier stores that has an advantage we're going to compare their actual sales to the sales within their location and that's what these z-scores are going to help us do so equals see double-click standardized highlight the score for curtis comma the overall mean for Curtis is location hit f4 or use the dollar signs to lock it in comma the standard deviation for this location f4 to lock it in or dollar signs hit enter now we can click and drag this down with the plus sign it's a cop with a form copy the formula down for everybody at Horizon Ridge do this two more times you're gonna be a pro by the time we're done here st double click standardize highlight my house comma the mean lock it in with f4 dollar signs comma the standard deviation lock it in with f4 dollar signs hit enter click go down here little plus sign or do the click and drag fill down method one more equals st there's our standardized the x value for caleb so how many savannah bars per week on average I saw over the past month comma the mean for Decatur location f4 to lock it in for dollar signs comma the standard deviation for Decatur f4 to lock in or use dollar signs hit enter and now we're ready to copy that formula down so remember they wanted to give a two hundred dollar bonus to the person that had the two highest Z scores or the most impressive number of Savannah bar sales within their location we're gonna suspension to the two with the most least impressive within their location so if we look here we'll be able to see kind of based on the Z scores who those people are so let's see what is our lowest z score it looks like our lowest z score is for mila here let's make that red saree Mila it's not looking good for you and then our second lowest z score I believe belongs to Miriam here sorry Mary looks like they're getting suspended right oh boo ironically they both have a 15 just kind of interesting so now let's look at the best performers so it looks like the highest z-score belongs to Lee seen here it's a making it green maybe bolduk that's a little bit it's hard to see and then the next highest z-score looks like it belongs to Curtis at the horizon Ridge location make it bold so check it out z-scores allow you to compare how extreme or impressive or not impressive scores are from completely different distributions it wouldn't be fair to just compare these scores because they're coming from different distributions we don't really know how impressive those sales are until we compare them to the overall sales at their location and the typical difference in sales at their location so you just use these scores to make decisions based on comparing different scores from different distributions so congratulations to Curtis and lysene sorry Miriam and Mia

Channel: Statistics and Research with Dr. Wendi L. Benson

Views: 1,412

Rating: 5 out of 5

Keywords: z-scores, Excel, Excel 2016, standardize

Id: WQcrt-x9w6U

Length: 16min 47sec (1007 seconds)

Published: Fri Oct 20 2017