Create a bell curve (normal distribution curve) in Excel VBA

Steps to Create a Bell Curve

1. Calculate the values for the normal distribution (probability density function).
2. Create a chart based on these values.
3. Customize the chart to display a smooth curve.

VBA Code for Creating a Bell Curve

Sub CreateBellCurve()
    ' Define parameters for the normal distribution (mean and standard deviation)
    Dim mean As Double
    Dim stdDev As Double
    Dim i As Integer
    Dim x As Double
    Dim y As Double
    Dim numPoints As Integer
    Dim startX As Double
    Dim endX As Double
    Dim rangeX As Range
    Dim rangeY As Range   
    ' Initialize normal distribution parameters
    mean = 0            ' Mean of the normal distribution
    stdDev = 1          ' Standard deviation of the normal distribution
    numPoints = 100     ' Number of data points for the curve
    startX = -5         ' Starting value for the X-axis
    endX = 5            ' Ending value for the X-axis   
    ' Calculate the X and Y values for the bell curve
    For i = 1 To numPoints
        ' Calculate the X value for each point
        x = startX + (endX - startX) * (i - 1) / (numPoints - 1)
        ' Calculate the Y value using the probability density function
        y = (1 / (stdDev * Sqr(2 * WorksheetFunction.Pi()))) * _
            Exp(-((x - mean) ^ 2) / (2 * stdDev ^ 2))       
        ' Place the values into the Excel cells (Column X and Y)
        Cells(i, 1).Value = x
        Cells(i, 2).Value = y
    Next i   
    ' Define the ranges for the chart data
    Set rangeX = Range(Cells(1, 1), Cells(numPoints, 1))
    Set rangeY = Range(Cells(1, 2), Cells(numPoints, 2))   
    ' Create a scatter plot (XY chart)
    Dim chart As Chart
    Set chart = Charts.Add
    With chart
        .ChartType = xlXYScatterSmooth
        .SetSourceData Source:=rangeX
        .SeriesCollection(1).XValues = rangeX
        .SeriesCollection(1).Values = rangeY
        .HasTitle = True
        .ChartTitle.Text = "Bell Curve (Normal Distribution)"
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Text = "X Value"
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Text = "Probability Density"
    End With
End Sub

Explanation of the Code

1. Define Parameters for the Normal Distribution:
   • mean: The mean of the normal distribution (set to 0 in this example).
   • stdDev: The standard deviation of the normal distribution (set to 1 in this example).
   • numPoints: The number of data points to calculate for the curve.
   • startX and endX: The range for the X-axis of the curve (set from -5 to 5 here).
2. Calculate the Values:
   • For each point, the x value is calculated as a regular increment between startX and endX.
   • The y value is then calculated using the probability density function (PDF) of the normal distribution:

y=σ2π​1​exp(−2σ2(x−μ)2​)

where:

• • • μ is the mean (0 here),
    • σ is the standard deviation (1 here).

3. Create the Chart:

• • The x and y values are placed into Excel columns A and B.
  • A « Smooth XY Scatter » chart is created, which represents the bell curve.
  • Titles for the chart and axes are added for clarity.

How to Use the Code

1. Open the VBA Editor:
   • Press Alt + F11 to open the VBA editor in Excel.
2. Create a New Module:
   • In the VBA editor, go to Insert -> Module.
3. Paste the Code:
   • Paste the code into the new module.
4. Run the Macro:
   • Close the VBA editor and return to Excel.
   • Press Alt + F8, select CreateBellCurve, and click Run.

Result

The code will generate a smooth bell curve (normal distribution curve) in Excel, where the mean is 0 and the standard deviation is 1. You can adjust the parameters (mean, standard deviation, etc.) to customize the curve as you like.