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.
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
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.