Mehedi Hasan
Fortunately, many of us use Excel in our business organizations. In any business organization, we use Excel to organize data as per need and make databases for the future. Moreover, one interesting thing is that we can create a gaussian distribution chart easily in Excel. However, I have used Microsoft Office 365 for the purpose of demonstration, and you can use other versions according to your preferences. In this article, I will show you a step-by-step procedure to create a Gaussian Distribution Chart in Excel. Hence, read through the article to learn more and save time.
Download Practice Workbook
You can download the workbook used for the demonstration from the download link below.
What is Gaussian Distribution Chart?
Usually, the Gaussian Distribution Chart is a graph that represents the normal distribution of a variable. Additionally, this is known as the Normal Distribution Curve or Bell Curve. In our nature, we see this distribution everywhere. For instance, if we survey marks from an exam, we will notice that most of the numbers are in the middle. However, the peak point of this curve signifies the mean of the distribution and it is lower on both sides. Moreover, this denotes the probability, which will be much lower for the extreme values (i.e. highest or lowest).
The features of the Gaussian Distribution Chart are –
First of all, 1 standard deviation of such mean is where 68.2% of the distribution falls.
Next, 95.5% of the distribution lies within the range of the average‘s two standard deviations.
Finally, 99.7% of the distribution is contained within a range of three standard deviations from the mean.
Step-by-Step Procedure to Create Gaussian Distribution Chart in Excel
Often, we need to create a gaussian distribution chart for certain business analytics, and the process becomes more interesting with Excel. However, the task is easy and simple. But you will need an arrangement in order to perform the operation properly. Hence, read through the steps below in order to complete the task. In pursuit of these, I am going to use AVERAGE, STDEV.P, and NORM.DIST functions. In general, the AVERAGE function returns the average of the numbers it takes as arguments. On the other hand, the STDEV.P function also takes a series of numbers as arguments and returns the standard deviations. Additionally, while the NORM.DIST function takes a value from a range of data, mean and standard deviation for the dataset, and a boolean value as its arguments and returns the normal distribution of the number in the number.
📌 Step 1: Select Dataset
For the purpose of demonstration, I have used the following sample dataset. Here, the dataset contains information about the Increase in Sales of each Employee in two consecutive months. Here, I am going to make a gaussian distribution chart for this increase in sales.
Read More: How to Create a Distribution Chart in Excel (2 Handy Methods)
📌 Step 2: Calculate Mean and Standard Deviation
- Initially, select cell D16 and write down the following formula in order to find the Mean increase in sales.
=AVERAGE(C5:C14)
- Then, press Enter.
- Similarly, select cell D17 and insert the formula mentioned below to calculate the standard deviation.
=STDEV.P(C5:C14)
- Finally, hit the Enter key to get the standard deviation.
Read More: Plot Normal Distribution in Excel with Mean and Standard Deviation
Similar Readings
- How to Make a Cumulative Distribution Graph in Excel
- Make a t-Distribution Graph in Excel (with Easy Steps)
- How to Plot Frequency Distribution in Excel (4 Easy Ways)
- How to Create Grade Distribution Chart in Excel (2 Examples)
📌 Step 3: Determine Normal Distribution
- In the beginning, click on cell D5 and insert the following formula.
=NORM.DIST(C5,$D$16,$D$17,FALSE)
- In the end, hit the Enter key and utilize the AutoFill tool in order to apply the formula to the entire column.
Read More: How to Plot Normal Distribution in Excel (With Easy Steps)
📌 Step 4: Create Gaussian Distribution Chart
- Firstly, select cell range D5:D14.
- Secondly, click the Insert tab.
- Thirdly, click on the Scatter command from the Charts group of commands.
- Fourthly, choose Scatter with Smooth Lines.
Read More: How to Create a Probability Distribution Graph in Excel (With 2 Examples)
Final Output
Last but not least, you will get your desired Gaussian Distribution Chart in Excel. However, the chart will appear as the image below after some modifications.
💬 Things to Remember
- First of all, it is better to sort the data before plotting the normal distribution. Else, an irregular curve may occur.
- Then, you need to select the chart to access the Chart Element menu and other chart editing options.
- Next, you can modify the chart according to your personal preference.
- Afterward, the mean and standard deviation of the data must be numeric. Else, it will show the #VALUEÂ error.
- Additionally, when you use the function carefully give all the required parentheses. Also, when you use the DIST function, you’ve to make mean and standard deviation as absolute cell references.
- At last, The standard deviation must be greater than zero. Or else, it will show you the #NUM! error.
Conclusion
These are all the steps you can follow to create a gaussian distribution chart in Excel. Overall, in terms of working with time, we need this for various purposes. I have shown multiple methods with their respective examples, but there can be many other iterations depending on numerous situations. Hopefully, you can now easily create the needed adjustments. I sincerely hope you learned something and enjoyed this guide. Please let us know in the comments section below if you have any queries or recommendations.
For more information like this, visit Exceldemy.com.
