 Hi, this is Dr. Don. I have a problem out of chapter 2 about the empirical rule and Z scores. The problem gives us the mean and standard deviation of a sample of farms. The mean X bar is 1500 and the sample standard deviation is 100. It says the data set has a bell shape distribution which is a clue that it's normal or approximately normal. It says use the empirical rule to determine which of the following farms are unusual, defined as more than two standard deviations from the mean, and very unusual more than three standard deviations from the mean. We're going to do this using basic Excel. Okay, I have an Excel workbook open with a blank worksheet and I've hidden the grid lines. You can go to view and click the grid lines on and off. I've cleared them off here just to make it a little bit easier to see. I've entered the sample mean of 1500 X bar and the sample standard deviation S of 100 and then the six values of X that we need to find the Z scores for. Remember the Z score formula is equal to X the value we're interested in minus the mean. Here we're using X bar and the sample standard deviation S. Now remember you can also use the population parameters which would be mu instead of X bar and sigma instead of S. But here we have a sample information so we use X bar and S. I'm going to click in this first yellow cell and my color notation is important. I use a light blue to tell me that's just raw data I've typed into a cell. I use light yellow to say that's a cell where I have a formula. Now we enter formulas by clicking in the cell hit equal to start entering the formula here. We want to put this formula in and we need to tell Excel to do this subtraction before it does division. Remember there's an order of operations that Excel will follow like you had to learn in high school. Here we use parentheses to guide Excel. I'm going to open up. I'm going to click on my X value there in B5. I'm going to subtract my mean X value up there in B1. Now I want to be able to copy this formula down so I can complete this spreadsheet more quickly. And I'm going to use a function key the F4 function F4. Well I've got that B1 selected and it enters the dollar signs before the column and the row designator. And that makes this an absolute reference which means I can drag the formula down and it will always refer back up here to cell B1. So I want to close that part with a closed parentheses. I want to divide by hitting the slash. And I want to divide by my standard deviation in B2. And again we use the function F4 function F4 to convert that to an absolute reference that I can click enter. And I want to show you how to we can then drag it down because we've got those absolute references. I'm going to just click in that cell get my cursor over to the black plus and then I can drag down to copy that formula and it copies it all the way down. And you can see as we go down it changes the first cell B6 is now X2 that's a relative reference but the absolute cells were locked and didn't change. One final thing I want to show you here I'm going to expose those formulas to help me remember Start typing FORM for formula and Excel will offer up formula text which is what I want. I'm going to double click that to enter it. I need to give it the cell reference. I want to use this one here to the left and just hit enter. Now I've got the formula in that cell and I can just drag that formula down to expose the other five formulas to help me remember what's in those cells. Okay now let's look at this. The first Z score for 1331 is minus 1.69. If I look over here on my empirical chart which shows 68% with the one standard deviation 95 within two standard deviations 99.7 within three standard deviations minus 1.69 would be somewhere in here. That's 1331 which is not more than two standard deviations so that's not unusual. 1787 is over here and you always should draw a sketch. That's more than two standard deviations obviously as we can tell by our Z score so that is unusual. If I go down here I can find 1120 which is minus 3.8 which is way over here on the very very small left side way beyond three standard deviations from the mean so that is unusual too. So I hope this helps. Learn to use basic excel. It can go a lot faster than this video that I'm trying to show you and then you can by formatting and labeling this and then saving your excel work with a good name you can reuse this the next time you need to find Z score. So I hope this helps.