given a data set, how do you know whether to calculate sigma or s? This is a topic that many people are looking for. bluevelvetrestaurant.com is a channel providing useful information about learning, life, digital marketing and online courses …. it will help you have an overview and solid multi-faceted knowledge . Today, bluevelvetrestaurant.com would like to introduce to you Find Number of Data Points within 1, 2 or 3 St Deviations in Excel. Following along are instructions in the video below:
For this video. What i want to do is to show you how to use use excel to be able to figure out how many of your data points are one two or three standard deviations of the mean all of the other information that i already have up here. I do have videos on how to come up with all of these so i have used excel formulas in order to fill in all of these cells.
That way if i change anything over here so like maybe if i notice that i typed this in wrong. And it was really supposed to be five thousand six hundred when i hit enter. It automatically updates everything else so it changes everything im gonna go back to control z.
Because i didnt really want to do that control z always undoes whatever you lasted if you accidentally make a mistake so within one standard deviation. What that means remember is that if you have your model. Its gonna be centered at the mean and within one standard deviation means that were going to take the mean so im going to do equals.
The mean minus. The standard deviation to get one standard deviation below the mean and then i would do the same thing for the next time. But this time i want to do minus two times.
The standard deviation and then im going to do the same thing for equals. The mean minus three times. The standard deviation and we know that we wont have any that are more than that are lower than negative six thousand.
So its impossible to get more than three standard deviations below the mean in this particular data set. It is okay to have negatives down here. It just means that its not possible so.
Its not possible to be three standard deviations below the mean in this case. Okay for the upper limit what im going to do is im going to do equals the mean and this time. Im gonna add the standard deviation because the upper means.
Its the positive so were adding to get to the next one and then im going to do equals. The mean plus two times. The standard deviation and we just use the asterisk symbol four times and then the last one were going to do equals the mean plus three times the standard deviation okay now to find the frequency.
What im going to do is im going to use a different frequency formula than i used up here for this one this gives you the cumulative frequency. And if you want to use this formula. You would have to take the frequency that fall by the end of this upper limit.
And then you would have to subtract everything thats on the below this one for the first one. Its fine you can use that but for the ones afterwards. This one gives you the cumulative frequency.
So everything that falls by this point.
So. If you dont want to have to do subtraction you can use another formula in excel. Called countifs countifs is for multiple criteria.
You dont use this. If you only have a single criteria for this one. I have more than one criteria.
So im going to because i want it to be between these two values. So what im gonna do is im going to come over here and im going to select my data and instead of highlighting the whole thing im just gonna hit ctrl shift and the down button ctrl shift and the down button and it automatically highlights that whole set of that whole column. So if you have a lot of data like if you have like a thousand rows thats the quickest way of doing it instead of having to scroll down with your mouse again that was ctrl shift and down.
So ctrl shift and down. And then im gonna put a comma behind this and it asks for my criteria. I want it to be greater than or equal to and notice how im putting this in quotation marks.
I have to put the greater than or equal to its telling me im looking for all values greater than or equal to and then im going to use the ampersand with the lower value. So i want all that are greater than or equal to this value comma. I want to use the same cell again so im going to come over here.
And im gonna again hit ctrl shift and down so it automatically populates a 1 through 25. And im gonna put in quotation marks. Again this time i want it to be less than or equal to the values that are in so i have to use the ampersand.
If you just had a set number like if you want it to be greater than or equal to 5. And less than or equal something else you can actually put those numbers inside the quotation marks. But if you want to reference a cell.
You have to use the ampersand and the cell that youre referencing. So my upper for this one is 13 and then im going to hit parentheses. And enter okay that tells me i have a frequency of 21 that fall between that are greater than 1872 and less than.
8 so 18000. 821 okay. So for this im just making sure i have all my formulas.
Correct i want to be able to drag this down here well if i drag this down here. Its gonna change my a 1 and a 25. So to keep those the same remember you put the dollar sign in front of them.
So im going to go back whoops control z.
Thats hold on control z. I dont know why it did that sometimes my mouse pad because i am working on a laptop. Ill accidentally hit the trackpad i did not mean to do that so im gonna put a dollar sign in front of both the a hmm.
I didnt get behind there the a and the 25 and instead of having to try to do this again. Im just going to highlight this part right here and ctrl c. And then im going to change this one to be the same thing control v.
So i just hit control c to copy and ctrl v to paste. Its the quickest way of doing it rather than right clicking. And so im going to keep those the same the d.
13 in the e 13. I want to change so now what i can do is i can just take and drag this down. And it will automatically populate so for this one.
It tells us that we have 22 that fall within two standard deviations and all 25 of them fall. Within three standard deviations. So my percentage for this one should be a hundred percent remember to calculate the percent.
Its just the frequency divided by the sum of all the frequencies so for this column what were going to do and im gonna format this cell because i wanted as a percent im actually going to format it to be percentage. I had already done that but to select it as percentage. We would put it here so its going to put the answer as a percent.
So what i want to do is i want to take this value right here this cell and i want to divide it by the sum of all my frequencies which i have right here the some of my frequencies. This formula for this one is just equal sum of these values here and i want the h. 11 to stay the same for all of them because my some of my frequencies does not change so im gonna hit enter for that so 84 of our data points fall within one standard deviation and then if i drag this down.
We have 88 of them fall within two standard deviations and we have a hundred percent of them fall within three standard deviations. So you can set this up like i said for my students that are working on their project. You can highlight this entire page and then copy it for the next one.
And then just change your data set remember that when you go to the proportions make sure that you pay attention to there being two decimal places. Instead of one decimal place. So just really be careful about that if you have any questions.
Please make sure you reach out to me. This is a different formula than i used up here you could use the same formula that i used up here. Its just going to its just nice to know different options as always thanks for watching.
If you have any questions please reach out to me. .
Thank you for watching all the articles on the topic Find Number of Data Points within 1, 2 or 3 St Deviations in Excel. All shares of bluevelvetrestaurant.com are very good. We hope you are satisfied with the article. For any questions, please leave a comment below. Hopefully you guys support our website even more.
