Excel Histogram with Normal Distribution Curve
HTML-код
- Опубликовано: 6 июл 2024
- Excel Histogram with Normal Distribution Curve
In this video, we will explain how you can create a histogram with a normal distribution curve in Excel.
0:00 Excel Histogram with Normal Distribution Curve Intro
0:44 Creating Histogram Table using Analysis Toolpak
2:31 Inserting the Histogram
4:22 Inserting the Normal Distribution Curve
Download Excel File:
Before we can ask Excel to insert the table, we first need to define the bins. Here we choose to start at 150 and then jump by 5 up to 200. Now we navigate to Data and select Data Analysis. A menu opens where we select Histogram, we press on OK and the Histogram menu opens.
Now we are ready to start creating the histogram and normal distribution curve. First, we select the histogram data, navigate to Insert, and select Scatter with Smooth Lines and Markers. The graph appears on the screen.
Next, we adjust the x-axis such that the curve is bigger and more centered on the screen. We double-click on the x-axis such that the Format Axis panel opens. Here, you go to the three bars icon, click on Axis Options and change the minimum and maximum to whatever suits you.
In the next step, we click on Add Chart Element, Error Bars, and More Error Bars Options. The Format Error Bars panel opens and you click on the three bars icon. Here, you select Minus, No Cap, Percentage, and set Percentage to 100.
We only need to remove the curve from the chart before we start with creating the normal distribution curve. To remove the curve, we click on it. This opens the Format Data Series panel. Here, we can select No line in the Line section. Under Marker, we navigate to Marker Options and select None.
Let’s start adding the normal distribution curve. To construct the curve, we first must define the data points. We will compute the corresponding y-values to the x-values between 145 and 205 as this covers the entire x-range of the histogram.
To compute the corresponding y-values, we first use the NORM.DIST function to compute the normal probability distribution curve. So, we type equals NORM.DIST of the x-value, mean, standard deviation and finally we insert FALSE to indicate we want the probability and not the cumulative distribution function.
Now we are ready to add the normal distribution curve to the chart. This can be done by selecting the graph, then Chart Design, and pressing on Select Data. We click on Add, insert the x-values and y-values and press on OK.
To change this, we insert a new column and add the midpoints of the bins instead of the endpoints. Next, we click on the histogram and drag the purple square representing the x-values to the midpoints. The graph is now how we want.
This concludes our tutorial on Excel Histogram with normal distribution curve. I'm inspired by content creators as Leila Gharani and Teacher's Tech.
#Excel #Tutorials #Statistics
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
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.
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!!
This has helped me so much, thank you!
Thank you sooo much. I have been trying to find a guide to this for sooo long and finaly something what I understand.🙏🙏🙏👍👍👍👍👍👍👍👍
How did you determine the "mid point" here? and what is the difference between it and the "end point"? Thanks.
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))))
Ty so much for showing howto add the analysis toolpak
Lifesaver! Thank you so much!!
Saved an entire work my friend, congratulations
This really help a lot for me, Respect!
Very helpful video. Really impressed my colleagues with this one!
Thank you. It was very useful.
You're welcome. I'm glad to hear it
Awesome, very helpful!
You're welcome! Glad it was helpful
THANK YOU FOR THE EXCELENT EXPLANATION. COULD YOU PLS EXPLAIN WHEN ERROR BARS COME VERTICALLY WHAT CAN WE DO?
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
I have a data set with averages. Am I still able to use the normdist formula ? or is there another way ?
really good! Thanks.
I'm looking to get the percentage of the histogram outside of the curve, is that possible?
at 6:25 when I am changing scatter from no line to solid line, I am getting too many lines connected to each other.
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.
Where do you get the "X" value?
Good Work, really helpful
Another question. How can I calculate frequency -15 against bin value 150?
How can got x value from histogram please, good work thanks 😊
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
great video! 8/10
Why dont use answer to the questions about mean and standart deviation? How did you get it?
How can I add control limit lines on this histogram
Excellent
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.
How did U find the midpoint in the bin??
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.
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
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.
Do you have the workbook to this tutorial so I can follow? Thanks.
Sure, where can I send it to?
5:34 why is it multiplied by 5?
There is the certain formula to find bin width. ;)
how did you find the variance please?
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)
hello I am failing to download the excel sheet - is the link working ?
where can i download the excel sheet
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! 😂
Here student Number is 31. Your frequency distribution in Bins is more than 5000, How is the procedure to calculate like you
can any one help with file - failing to download
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/
Why are we multiply by 5000 sir
Because he has 5000 respondents, 5000 students
If this is the quickest way to do it, the developers should be fired for not having a trendline available in histogram
how you gonna make a tutorial video and then cut an edit in with new things done????
The worst video explaining this