Click here to Skip to main content
15,939,853 members
Articles / Productivity Apps and Services / Microsoft Office / Microsoft Excel

Converting discrete data points into a histogram using Excel VBA

Rate me:
Please Sign up or sign in to vote.
4.50/5 (2 votes)
3 Jun 2013CPOL4 min read 38.2K   579   9   3
This article gives a method for using Excel VBA to convert discrete data points into a histogram.


This article gives a method for using Excel VBA to convert discrete data points into a histogram. It utilizes user forms within VBA to retrieve data either from a worksheet in Excel itself, or from a text file stored separately. 


In many engineering fields there is often a great deal of test data gathered from various systems. This data can be pressure, temperature, voltage, current, speed, or whatever other type of data needs to be gathered. Typically, the test equipment used to gather and store this data keeps track of every data point as a set of discrete points at specific times. This type of data is often called time-history data, since it shows a time-based history of the data being collected. The scrolling line on the performance monitor on your PC is an example of this type of data.

In many cases, however, this time-history type of data is not very useful for mathematical or engineering analysis. That is where the histogram comes into play. The time-history data can be converted into what is often know as time-at-level data, or a histogram.

A histogram displays the number of times that the data being examined falls within a specified range. Typically a number of ranges, or bins, are used so that the entire range of data can be represented. 

Like most engineers, I had a need to turn time-history data into time-at-level data, so the solution was to create an Excel Add-In to help automate this task. There are many pieces of commercial software available that will do this task quickly and efficiently, but I had no budget for these, and Excel was available. 

I developed this solution a few years ago, and decided to make it into a Code Project article in response to this question on StackOverflow. 

Using the code

The majority of the code in this project is the user interface to get Excel to help the user select the data to be converted and to specify how the data should be divided. The actual mechanics of creating the userform and programming the controls on it is not the main subject of this article. 

Image 1

The above user form allows the user to select a number of options:

  • either 1-channel or 2-channel data conversion 
  • the data source, either a text file or a range of cells on a worksheet
  • the number of bins the data should be sorted into  

After the user selects the data, the minimum and maximum values are found. The user can then enter the number of bins to sort the data into. After number of bins is entered, the bin size is calculated and displayed.

The key piece of information to convey is the code to actually turn the time-history data into time-at-level data.

The PutDataInBins() method is used for that function, and the following code excerpt is where the actual conversion occurs: 

'Calculate what bin the data belongs in.
For RowNumber = 0 To NumberOfDataLines - 1
    BinNumber0 = Int((DataValueList(RowNumber, 0) - MinimumValue(0)) / BinSize(0))
    If chanDimension = 2 Then
        BinNumber1 = Int((DataValueList(RowNumber, 1) - MinimumValue(1)) / BinSize(1))
        BinNumber1 = 0
    End If
    BinCount(BinNumber0, BinNumber1) = BinCount(BinNumber0, BinNumber1) + 1

The key calculation to make the conversion is only one line of code:

BinNumber0 = Int((DataValueList(RowNumber, 0) - MinimumValue(0)) / BinSize(0))

BinNumber0 represents the number of the bin to place the current data point. DataValueList is the value of the current data point. MinimumValue represents the minimum vale for all data points in that channel, and BinSize is the calculated size of each bin. These calculations determine what bin number should have a count added to it for this data point.

The example shown in the user form screenshot above uses the sample data file, testdata.txt, that is part of the zip file download. This is one channel of data saved as time-history data, or sequential data points. Shown below is a graphical representation of these 10000 data points:

Image 2 

The add-in then converts the data into time-at-level, or histogram format, with the data being placed in 10 discrete bins. Shown below is the output and a chart created from the output data:

Image 3 

The add-in can be used as-is or modified to automate the chart creation process for the 1-channel conversion. 

Points of Interest 

Excel VBA is filled with all kinds of pitfalls and watch-outs, but this project is straightforward enough that it doesn't encounter any of the more serious issues.

Keep in mind, that unless you explicitly tell VBA differently, all arrays and collections are 1-based rather than 0-based. Even if you declare arrays to be 0-based, the built-in Excel collection objects are still 1-based. 


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Written By
United States United States
Employed as a hydraulic systems design engineer. I develop tools in VB.NET and C# to support this function.

Comments and Discussions

GeneralConverting discrete data points into a histogram using Excel VBA Pin
Member 1100699611-Aug-14 21:21
Member 1100699611-Aug-14 21:21 
QuestionPlease stop editing this Tip... Pin
OriginalGriff2-Jun-13 5:24
mveOriginalGriff2-Jun-13 5:24 
AnswerRe: Please stop editing this Tip... Pin
StewBob2-Jun-13 14:51
StewBob2-Jun-13 14:51 
Nobody's trying to sneak anything past anyone. After I posted this article, I checked back later and noticed it was a Tip. Assuming I had 'clicked the wrong box', I corrected what I thought was my mistake. No 'sneaking' involved.

Regarding the status of this submission as a Tip:
I thought a tip was more along the lines of "Hey did you know that the 'Windows-M' command will minimize all the open windows in Winodws 7?"
I'm not sure if you have read the article or looked at the source, but this is a custom-programmed solution with about 800 lines of code. It's not "hey, did you know Excel has an AVERAGE function?"

I realize that it's not a full-featured, stand-alone application, but a considerable amount of work went into it.

Am I mistaken on what constitutes a Tip versus an Article? This is certainly more cogent and complete than a great many 'articles' that I see here every day.

There are two kinds of people. Those who need closure

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.