(Solved) – Plotting histogram with a normality plot overlay misaligned

(solved)-–-plotting-histogram-with-a-normality-plot-overlay-misaligned

What i am trying to do is to have a normality plot or a “bell curve” as some might say, on top of a histogram. I have used a code to create my histogram and use another code to get the normality data which is shown below. At the moment, all i want to know is how to merge the histogram which i have created using the code and a XY scatter smooth line graph(Normality plot) created by manually creating a chart using the normality data i obtain from the code.

I have tried to combine them together which made it look like this
enter image description here

And as you can see, the normality plot is not really align with the histogram. I have found out that that is because of the difference in the axis which cause it to be misaligned. My normality data is taken from the entire data itself and the histogram is using the count values. However when i find the mean and Standard deviation of the count values and change the data according to it, this happens

enter image description here

With this, it is even more misaligned.

Code for histogram.

Sub MakeHistogram()

    Dim src_sheet As Worksheet, Graph_sheet As Worksheet
    Dim selected_range As Range
    Dim r As Integer, firstOne As Integer
    Dim percent_cell As Range
    Dim num_percent As Integer
    Dim count_range As Range, bin_range As Range
    Dim new_chart As Chart
    Dim lRow As Long
    Dim RngToCover As Range, Chtob As ChartObject
    Dim Snum As Long, Bnum As Long

    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
        .EnableEvents = False
        .DisplayStatusBar = False
    End With

    With Sheets("Data")
        lRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        Set selected_range = .Range(.Cells(2, 1), .Cells(lRow, 1))
    End With
    Set src_sheet = Sheets("Data")
    Set Graph_sheet = Sheets("Graphs")

    num_percent = selected_range.Count

    ' See how many bins we will have.
    Const BIN_SIZE As Integer = 5
    Dim num_bins As Integer
    num_bins = 150  BIN_SIZE

    ' Make the bin separators.
    Graph_sheet.Cells(1, 2) = "Bins"

    For r = 1 To num_bins - 1
        Graph_sheet.Cells(r   2, 2) = r * BIN_SIZE
    Next r

    Graph_sheet.Cells(r   2, 2) = 150
    ' Make the counts.
    Graph_sheet.Cells(1, 1) = "Counts"
    Set count_range = Graph_sheet.Range("A" & 3 & ":A" & num_bins   2)
    Set bin_range = Graph_sheet.Range("B" & 3 & ":B" & num_bins   2)

    count_range = WorksheetFunction.Frequency(selected_range, bin_range)

    ' Make the range labels.

    Graph_sheet.Cells(1, 3) = "Ranges"
    For r = 1 To num_bins
    firstOne = 1
    If r = 1 Then firstOne = 0
        Graph_sheet.Cells(r   2, 3) = "'" & _
            (5 * (r - 1))   firstOne & "-" & _
              5 * (r - 1)   5
        Graph_sheet.Cells(r   2, 3).HorizontalAlignment = _
            xlRight
    Next r
    r = num_bins

    Graph_sheet.Cells(r   1, 3).HorizontalAlignment = xlRight

    ' Make the chart.
    Set new_chart = Charts.Add()
    With new_chart
        .ChartType = xlColumnClustered
        .SetSourceData Source:=Graph_sheet.Range("A" & 3 & ":A" & _
            num_bins   2), _
            PlotBy:=xlColumns
        .Location where:=xlLocationAsObject, _
            Name:="Graphs"
    End With

    'Get the largest and smallest number
    Bnum = WorksheetFunction.Max(selected_range.Value)
    Snum = WorksheetFunction.Min(selected_range.Value)

    With Graph_sheet
        Set RngToCover = .Range(.Cells(6, 5), .Cells(23, 11))
    End With

    With ActiveChart

        With .ChartArea.Format
            ' White background
            .Fill.ForeColor.RGB = RGB(255, 255, 255)

            ' Red border
            With .Line
                .ForeColor.RGB = RGB(100, 100, 100)
                .Weight = 2
            End With
        End With

        .HasTitle = True
        .ChartTitle.Characters.Text = title & " Histogram"
        With .Axes(xlCategory, xlPrimary)
            .HasTitle = True
            .AxisTitle.Characters.Text = xStr
        End With
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Count"

        ' Display percentage ranges on the X axis.
        .SeriesCollection(1).XValues = "='" & _
            "Graphs" & "'!R" & 3 & "C3:R" & _
            num_bins   2 & "C3"

        Set Chtob = .Parent
        Chtob.Height = RngToCover.Height
        Chtob.Width = RngToCover.Width
        Chtob.Top = RngToCover.Top
        Chtob.Left = RngToCover.Left

        .SeriesCollection(1).Select
        With .ChartGroups(1)
            .Overlap = 0
            .GapWidth = 0
            .HasSeriesLines = False
            .VaryByCategories = False
        End With
    End With

    'Add statistics

    With Graph_sheet
        r = num_bins   4

        .Cells(r, 1) = "Average"
        .Cells(r, 2) = "=AVERAGE(Data!" & selected_range.Address & ")"

        .Cells(r, 4) = "Min"
        .Cells(r, 5) = Application.WorksheetFunction.Min(selected_range)

        .Cells(r, 7) = "Q1"
        .Cells(r, 8) = WorksheetFunction.Quartile(selected_range, 1)

        .Cells(r, 10) = "Median"
        .Cells(r, 11) = WorksheetFunction.Quartile(selected_range, 2)

        r = r   1

        .Cells(r, 1) = "StdDev"
        .Cells(r, 2) = "=STDEV(Data!" & selected_range.Address & ")"

        .Cells(r, 4) = "Max"
        .Cells(r, 5) = Application.WorksheetFunction.Max(selected_range)

        .Cells(r, 7) = "Q3"
        .Cells(r, 8) = WorksheetFunction.Quartile(selected_range, 3)

        .Cells(r, 10) = "IQR"
        .Cells(r, 11) = WorksheetFunction.Quartile(selected_range, 3) _
            - WorksheetFunction.Quartile(selected_range, 1)

    End With

    With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
        .EnableEvents = True
        .DisplayStatusBar = True
    End With

    Graph_sheet.Columns(1).AutoFit

End Sub

Code to obtain normality plot’s x and y axis points which can create a XY scatter graph smooth line to plot the normality graph.

Sub GetNormalityData()

    Dim xMean As Variant
    Dim xSigma As Variant
    Dim xFirst As Variant
    Dim xLast As Variant
    Dim Nstep As Long
    Dim X As Variant
    Dim y As Variant
    Dim sRow As Long
    Dim StepValue As Variant

    ' enter values of mean, sigma, xfirst, xlast, Nstep
    With ActiveSheet
        xMean = .Range("B1")
        xSigma = .Range("B2")
        xFirst = .Range("B3")
        xLast = .Range("B4")
        Nstep = .Range("B5")
    End With

    StepValue = (xLast - xFirst) / (Nstep - 1)

    sRow = 0
    X = xFirst

    With Range("D1")
        Do While X <= xLast
            .Offset(sRow, 0) = X
            y = Application.WorksheetFunction.NormDist(X, xMean, xSigma, False)
            .Offset(sRow, 1) = y
            X = X   StepValue
            sRow = sRow   1
        Loop
    End With

End Sub

I have also attached an example workbook in case you need one here. To get the graphs, i have created the normality plot chart first which i then copied the histogram count values into it and changing it to column clustered graph. If there is any questions you want to ask, please, do not hesitate.

Leave a Reply

Your email address will not be published. Required fields are marked *