Omg this was very helpful I have a group research ang I'm assigned to the Organizing ang Presentation of our data. So this helped me alot for my FDT and visualization for the normality of distribution. Thankyou verymuch!!
The midpoint of the bin is half its width. So for this example with a bin width of 5 (as the difference between 150 and 155, is 5), the midpoint in 2.5.
THANK YOU there isnt any video like yours, i find this hardly it was very non understandable how can distribution be like a curve, how create by own and understand the whole process, Thanks a lot))))
This is nice! I love the way you hacked Excel to overlay a normal distribution over a "histogram" as this is impossible to do with normal Excel tools. One comment from me: the built in histogram add-in kills the formula. So if you plug in different data you need to redo the bins and frequency again. I would prefer to use the frequency function and a smart function to create the bins from the range and a given number of bins.
When you're correcting the y-value for the normal distribution (norm.dist) did you say add 5000 times 5? (=norm.dist(cell, cell, cell)+5000*5? Or =norm.dist(cell,cell,cell) *5000*5? Your cursor covers the exact spot to see if you added or multiplied the 5000*5.
Yes he did but it is "*" and not "+". You can also use = norm.dist(cell, cell, cell)*COUNT(range of cells of main data)*(The difference between two bin data points) For the difference use $ signs to fix the cells (or F4) Then you don't have to manually calculate the number of data points you have manually and don't have to calculate the bin difference either and can use this for any data set.
Thank you so much however, when I try adding the curve, my normal graph (lets say series 1) goes down. Now I have a curve way over the actual graph. Theres jut something Im missing
The sum after applying norm.dist function is not coming 1 in the y column (before applying correction multiplication). what to do please help. My data does not contain integer values; it has decimal values (float), and I have taken all data values as X.
You should put a link to download the excel template that you used in this tutorial so that viewer could follow what you did, otherwise I will give you a thumb up.
I cant seem to move the normal distribution to the right because when I click on the histogram it doesn't highlight the bins in purple but the X values
Hi, I'll try to explain. When you click on the histogram, you need to make sure that you click in the middle of and at the top of one of the bins. That way you select the histogram. When you click on the histogram nearby the normal distribution curve, this curve is selected instead. Another way to move the normal distribution to the right is by clicking on the graph, selecting Chart Design in the ribbon. Next, you click on Select Data. A menu opens where you see two series. Series1 should be the histogram. You click on this one, select Edit, and change the x-values to the midpoints of the bins by inserting the range H3:H13. You click on Ok twice and the normal distribution should move to the right. Hope this helps.
Hi, I'll try to explain. If you plot a normal distribution curve, the area under the curve is 1 by definition, while the area under the curve is way bigger for our histogram. If you don’t correct the y-values for this, the normal distribution curve will appear way too small on the graph compared to the histogram. So, we need to correct the y-values such that the size of the area under the curve for the normal distribution is the same as the size of the area under the curve for the histogram. As the area under the curve for the normal distribution is 1, we only have to multiply this by the area under the curve for the histogram in order to end up with the same size. To compute the size of the histogram we have to sum the size of each rectangle that is part of the histogram. To compute the size of one rectangle we have to multiply the width by the length. The width is 5 as we chose a bin width of 5 for our histogram and the length is the number of observations that falls into that bin. Since we have to do this computation for each bin and hence we capture all observations, it suffices to multiply the bin width by the total number of observations. I hope this helps.
The total number of observations 5000 and width of bin 5 I am right, sorry if I bother you again. Can I have your work Excel sheet please? High priority!
@@Aishasadventures7809 Hi, sorry for the delayed response. I added the file to the website. You can download it from: softtechtutorials.com/microsoft-office/excel/excel-histogram-with-normal-distribution-curve/
Can I save the work and reuse it by refreshing the input data and have the curve updated accordingly? I’d like to use it to show distribution of students’ marks as there are new quizzes and exams without having to do the steps from the beginning.
Thank you! This is the best explanation and practical instruction on this topic that I have seen on the internet.
You're welcome! I really appreciate the kind feedback.
Oh my god! Finally! THANK YOU VERY MUCH! You saved our team! Much love and respect from department of biochemistry in Greece! (Eva, Spyros, Giannis
This is probably the best tutorial on this topic. Thank you!!
Wow. This really saved my day. So much better than the other explanations I clicked through before finding this. Thanks!
Omg this was very helpful I have a group research ang I'm assigned to the Organizing ang Presentation of our data. So this helped me alot for my FDT and visualization for the normality of distribution. Thankyou verymuch!!
Very helpful video. Really impressed my colleagues with this one!
bro the moment when you created the histogram was sickkkk
Ty so much for showing howto add the analysis toolpak
How did you determine the "mid point" here? and what is the difference between it and the "end point"? Thanks.
The midpoint of the bin is half its width. So for this example with a bin width of 5 (as the difference between 150 and 155, is 5), the midpoint in 2.5.
Saved an entire work my friend, congratulations
THANK YOU there isnt any video like yours, i find this hardly it was very non understandable how can distribution be like a curve, how create by own and understand the whole process, Thanks a lot))))
This is nice! I love the way you hacked Excel to overlay a normal distribution over a "histogram" as this is impossible to do with normal Excel tools.
One comment from me: the built in histogram add-in kills the formula. So if you plug in different data you need to redo the bins and frequency again. I would prefer to use the frequency function and a smart function to create the bins from the range and a given number of bins.
Thank you sooo much. I have been trying to find a guide to this for sooo long and finaly something what I understand.🙏🙏🙏👍👍👍👍👍👍👍👍
If this is the quickest way to do it, the developers should be fired for not having a trendline available in histogram
Lifesaver! Thank you so much!!
This really help a lot for me, Respect!
Good Work, really helpful
THANK YOU FOR THE EXCELENT EXPLANATION. COULD YOU PLS EXPLAIN WHEN ERROR BARS COME VERTICALLY WHAT CAN WE DO?
really good! Thanks.
thank you so much for your great explanation. I really understood it. how about Excel Histogram with Normal Distribution Curve for a Likert Scale Data
This has helped me so much, thank you!
Thank you. It was very useful.
You're welcome. I'm glad to hear it
I'm looking to get the percentage of the histogram outside of the curve, is that possible?
Where do you get the "X" value?
Awesome, very helpful!
You're welcome! Glad it was helpful
When you're correcting the y-value for the normal distribution (norm.dist) did you say add 5000 times 5? (=norm.dist(cell, cell, cell)+5000*5? Or =norm.dist(cell,cell,cell) *5000*5? Your cursor covers the exact spot to see if you added or multiplied the 5000*5.
Yes he did but it is "*" and not "+".
You can also use = norm.dist(cell, cell, cell)*COUNT(range of cells of main data)*(The difference between two bin data points)
For the difference use $ signs to fix the cells (or F4)
Then you don't have to manually calculate the number of data points you have manually and don't have to calculate the bin difference either and can use this for any data set.
Thank you so much however, when I try adding the curve, my normal graph (lets say series 1) goes down. Now I have a curve way over the actual graph. Theres jut something Im missing
The sum after applying norm.dist function is not coming 1 in the y column (before applying correction multiplication). what to do please help. My data does not contain integer values; it has decimal values (float), and I have taken all data values as X.
How did you get the mean and the standard deviation so fast
He just did not show them in the video. Use average function and STDEV.S(range)
Why dont use answer to the questions about mean and standart deviation? How did you get it?
at 6:25 when I am changing scatter from no line to solid line, I am getting too many lines connected to each other.
great video! 8/10
You should put a link to download the excel template that you used in this tutorial so that viewer could follow what you did, otherwise I will give you a thumb up.
5:34 why is it multiplied by 5?
There is the certain formula to find bin width. ;)
@@О.Амгаа is 5 constant?
Excellent
I have a data set with averages. Am I still able to use the normdist formula ? or is there another way ?
I couldn't find the link to downloading the data you used.
How can got x value from histogram please, good work thanks 😊
I cant seem to move the normal distribution to the right because when I click on the histogram it doesn't highlight the bins in purple but the X values
Hi, I'll try to explain. When you click on the histogram, you need to make sure that you click in the middle of and at the top of one of the bins. That way you select the histogram. When you click on the histogram nearby the normal distribution curve, this curve is selected instead.
Another way to move the normal distribution to the right is by clicking on the graph, selecting Chart Design in the ribbon. Next, you click on Select Data. A menu opens where you see two series. Series1 should be the histogram. You click on this one, select Edit, and change the x-values to the midpoints of the bins by inserting the range H3:H13. You click on Ok twice and the normal distribution should move to the right.
Hope this helps.
Another question. How can I calculate frequency -15 against bin value 150?
Here student Number is 31. Your frequency distribution in Bins is more than 5000, How is the procedure to calculate like you
👌☺️😍
Another question please why you multiply y by 5000*5 please
Hi, I'll try to explain.
If you plot a normal distribution curve, the area under the curve is 1 by definition, while the area under the curve is way bigger for our histogram. If you don’t correct the y-values for this, the normal distribution curve will appear way too small on the graph compared to the histogram.
So, we need to correct the y-values such that the size of the area under the curve for the normal distribution is the same as the size of the area under the curve for the histogram. As the area under the curve for the normal distribution is 1, we only have to multiply this by the area under the curve for the histogram in order to end up with the same size.
To compute the size of the histogram we have to sum the size of each rectangle that is part of the histogram. To compute the size of one rectangle we have to multiply the width by the length. The width is 5 as we chose a bin width of 5 for our histogram and the length is the number of observations that falls into that bin. Since we have to do this computation for each bin and hence we capture all observations, it suffices to multiply the bin width by the total number of observations.
I hope this helps.
5000 how many students do you have and 5 for bin or it’s constant??
@@SoftTechTutorials89 very helpful thanks a lot
The total number of observations 5000 and width of bin 5 I am right, sorry if I bother you again. Can I have your work Excel sheet please?
High priority!
@@Aishasadventures7809 Hi, sorry for the delayed response. I added the file to the website. You can download it from: softtechtutorials.com/microsoft-office/excel/excel-histogram-with-normal-distribution-curve/
hello I am failing to download the excel sheet - is the link working ?
are we supposed to take mean and standard deviation of our x values or original values
the mean and stdev of the original values. he posted the link where downloadable file could be accessed in the comment section
Do you have the workbook to this tutorial so I can follow? Thanks.
Sure, where can I send it to?
How can I add control limit lines on this histogram
how did you find the variance please?
5.28
Brother, where did the 5,000 come from?
the time I deleted the error bars, my graph was gone. What happened
where can i download the excel sheet
Why are we multiply by 5000 sir
Because he has 5000 respondents, 5000 students
can any one help with file - failing to download
Can I save the work and reuse it by refreshing the input data and have the curve updated accordingly? I’d like to use it to show distribution of students’ marks as there are new quizzes and exams without having to do the steps from the beginning.
Do your own homework! 😂
Why do we have to multiply by 5000?
how you gonna make a tutorial video and then cut an edit in with new things done????
🎉😮😢😢😊
The worst video explaining this
How did U find the midpoint in the bin??