Assume you have real values (negative, positives, zeros) in Scores and the bin width is real number (etc 2.93) so bins (0, 2.93, 5.86, ). I get a big tail of zeros by your method in Histogram 1 but everything else seems to work.
. Histograms in Excel with VBA macros On this page I show how to make a histogram fast and easy in Excel with VBA macro code. I also show how to make a bell shaped curve with normal distribution and the same standard deviation and mean value as the histogram. A histogram is a graphical representation of data distribution. It could be the population's age distribution or quality data, and from the shape you get a pretty good idea if it is a normal distribution or not. To be sure about the normal distribution-thing you need to test statistically, but I'll leave that to you - this is about VBA!
![How To Modify Bin Width For A Histogram In Excel Mac How To Modify Bin Width For A Histogram In Excel Mac](http://www.tech-recipes.com/wp-content/uploads/2012/10/excel-2011-histogram.png)
You can make histograms manually, if you have installed Analysis Toolpak, but it is tedious work (see a guide ) - it is much, much faster to use macros. In the first example I show how to make a histogram with a bell shaped curve like in Microsoft's example (like the image at the top), and you don't need Analysis Toolpak. Shows how to make a histogram with a user defined number of columns. The macro calculates interval size, counts frequencies and makes the histogram chart. You can copy the code and paste it into a VBA module, but it is easier to with the code.
It also contains a simple userform, which is only referred to on this page. The VBA code on this page assumes that the source data for the histogram is in another workbook, and the histogram will be put on a new sheet in that workbook. Histogram with bell shaped curve In the first histogram the chart's number of columns (intervals) is fixed to 8 and the interval size is the data's standard deviation. To make the bell shaped curve we use 2000 'random' numbers with the same standard deviation and mean value as the data for the histogram. The trick in doing this (getting the same standard deviation etc.) is to use the worksheet function 'NormInv' together with VBA's random numbers generator, 'Rnd'. You'll se how later, when we get to the procedure 'RandomNumbers'.
At module level you need to declare 2 public variables: Public bAbort as Boolean and Public bBell as Boolean. Sub BellShape 'The start procedure that calls the 'procedure 'OpenForm'. 'bAbort and bBell are Boolean variables 'declared on module level. BBell = True OpenForm If bAbort Then bAbort = False Exit Sub End If End Sub '. Sub OpenForm 'Opens the workbook containing source 'values or asks the user to do so. 'This procedure is used in making both 'types of histograms. On Error GoTo ErrorHandle 'If there is only one workbook open '(this one), we show a file open dialogue.
If Workbooks.Count = 1 Then 'Open file dialogue With Application.FileDialog(msoFileDialogOpen).Title = 'Open workbook with values for histogram' 'Allow only one file to be opened.AllowMultiSelect = False 'Show files with the extension 'xl.' .Filters.Add 'Workbooks', '.xl.' , 1.Show If.SelectedItems.Count 0 Then Workbooks.Open (.SelectedItems(1)) Else 'If the user cancelled bAbort = True Exit Sub End If End With ElseIf Workbooks.Count = 2 Then 'If there are 2 open workbooks 'we activate the other one. If Workbooks.Item(1).Name = ThisWorkbook.Name Then Workbooks(2).Activate Else Workbooks(1).Activate End If ElseIf Workbooks.Count 2 Then Workbooks(Workbooks.Count).Activate End If 'frmPickSheet is just a simple userform asking the user 'to activate the workbook with the source data - see 'the workbook download.
![Calculate Calculate](/uploads/1/2/5/3/125396051/194710211.jpg)
FrmPickSheet.Show vbModeless Exit Sub ErrorHandle: MsgBox Err.Description & ' Procedure OpenForm' End Sub '. 'Make a simple Userform with a label and 2 command buttons. 'Name it 'frmPickSheet'. 'The label should ask the user to select the sheet with 'source data, and it should have the following code: Private Sub CommandButton1Click Unload Me If bBell Then Module1.Histogram2 Else Module1.Histogram End If End Sub Private Sub CommandButton2Click Unload Me End Sub '. Sub Histogram2 'Makes a histogram and a bell shaped curve 'with normal distribution. The curve shape 'and interval size of the histogram are based 'on the data set's mean value and standard deviation. Sub Simple 'The start procedure that first calls the 'procedure 'OpenForm' and then 'Histogram'.
'bAbort is a Boolean declared on module level. BBell = False OpenForm If bAbort Then bAbort = False Exit Sub End If End Sub '.
Sub Histogram 'Makes a 'simple' histogram.