Need help making a dynamic histogram

[keiflill]

Hey everyone, I have what should be a fairly simple problem that has been taking me forever to figure out. Here is my situation (Using Excel 07):

I'm going to be inputting 100 numbers into a column (Say B2:B101) and need to make a histogram out of that at intervals of 1 from the lower bound to the upper bound of those 100 numbers.

I've used =min and =max to define the bounds and made a box set equal to 1 called interval. From there I don't really know how to carry on.
What makes this histogram more difficult than others is the varying possibilities of instances. The histogram could have anywhere from 100 individual bars of single frequency to one bar of 100 instances, or anywhere in-between.

I am familiar with the =countif function. What I was thinking, or trying to do is have the countif go over the input column in a loop, from the lowest number to the highest number. Then maybe input those counts into a new array that can be used for the histogram?

Frankly, any method is appreciated, efficiency isn't too important, I just need it to work. Unfortunately, I can't do this in Matlab because it needs to be accessible by my colleagues on various computer too.

Just a little disclaimer, I know that some people post homework questions or something trying to get an answer. This is not the case, this is for my job (a job that claimed to require no background in statistics until last week).

Oh yea, one last thing, what i'm making is a template, the number of intervals may vary from case to case, which is why it needs to be dynamic.

Any help is appreciated. Thanks

 

[keiflill]

OK So I think I'm onto something. Just need a bit of help following through. Here's what I have (using a simplified sample)

Start with Column A having 5 digits. Like, 4 4 2 6 4 for example.
So Now i'm going to define a cell as "Lower Bound" using =min(A1:A5) .... = 2
Repeat using another cell for "Upper Bound" using =max(A1:A5) ... = 6

Recall that my histogram needs to go at intervals of 1. So what i'd do now is find out how many integers are between Lower and Upper Bound.
This is done by defining a new cell as "Trows" (stands for table rows)
Trows = (Upper Bound) - (Lower Bound) + 1.
Trows = (6 - 2 + 1) = 5

Now this is where i kind of lose ability. I need to create a table with "Trows" rows. The first row will be the value of "Lower Bound" and increase in intervals of 1 up to "Upper Bound".

So for this example, we would have a table with rows labeled 2 3 4 5 6.
Now I can do a countif that references each of those integers within the number set and outputs its frequency it in the adjacent column.

For those of you who have actually read this far, I think you will agree that this will work. What i need help with is creating a dynamically changing table that constructs itself based on the value of "Trows" that is bound by "Upper bound" and "Lower Bound"

Any ideas?

 

[mgirvin]

Maybe this will work:

Create a workbook with the name: DynamicFrequencyTableAndHistogram.xlsx
Create a sheet with the name 'DH'

We need to create some dynamic named ranges for the number data (Defined Names). To open the Name Manager use the keyboard shortcut Ctrl + F3. Click the New button. Create this formula:

Data =DH!$A$2:INDEX(DH!$A$2:$A$201,COUNT(DH!$A$2:$A$201))

Names go in the ‘Name’ textbox.
Formulas go in the ‘Refers to’ textbox

In column A put your number data. Enter formulas in D column. All as seen below:

Excel Workbook
	A	B	C	D
1	Data		Min	2
2	4		Max	12
3	4		Range (Trows)	11
4	2
5	6
6	4
7	10
8	8
9	12
10	4
11	5
DH

Excel 2010

Cell Formulas
Range	Formula
D1	=MIN(Data)
D2	=MAX(Data)
D3	=D2-D1+1

Excel Workbook
Name	Refers To
Data	=DH!$A$2:INDEX(DH!$A$2:$A$201,COUNT(DH!$A$2:$A$201))
Workbook Defined Names

Create the formula in F2 for the dynamic category label (will be used for the horizontal axis in the histogram chart) and copy it down to cell F101. Then create the formula in G2 for the dynamic frequency calculation (will be used for the vertical axis in the histogram chart) and copy it down to cell G101.

Excel Workbook
	E	F	G	H
1		Categories	Frequency
2		2	1
DH

Excel 2010

Cell Formulas
Range	Formula
F2	=IF(ROWS(F$2:F2)>D$3,"",D$1+ROWS(F$2:F2)-1)
G2	=IF(F2="","",COUNTIF(Data,F2))

Excel Workbook
Name	Refers To
Data	=DH!$A$2:INDEX(DH!$A$2:$A$201,COUNT(DH!$A$2:$A$201))
Workbook Defined Names

For creating the chart we need to create some dynamic named ranges (Defined Names). To open the Name Manager use the keyboard shortcut Ctrl + F3. Click the New button. Create these two formulas:

Categories =DH!$F$2:INDEX(DH!$F$2:$F$101,MATCH(DH!$D$2,DH!$F$2:$F$101))
Frequency =DH!$G$2:INDEX(DH!$G$2:$G$101,MATCH(DH!$D$2,DH!$F$2:$F$101))

Names go in the ‘Name’ textbox.
Formulas go in the ‘Refers to’ textbox

Next we need to create the chart:

Highlight range G1:G12, then use the keyboard shortcut for the column Chart on the same sheet: Alt + F1.

In the Chart Tools Design Ribbon / Tab, go to the Data group and click on the Select Data button. In the Select Data Source dialog box click on the Legend Entries (Series) ‘Frequency’, then click the Edit button. In the Edit Series dialog box in the Series Values text box, select only the range ‘$G$2:$G$12’ – do not highlight the ‘=DH!’ part of the formula. Open the Paste Name dialog box with the keyboard shortcut F3 and double-click the Defined Name ‘Frequency’. Click OK. If you were to open the Edit Series dialog box you would see that the formula has transformed into this:

=DynamicFrequencyTableAndHistogram.xlsx!Frequency

Click on the Horizontal (Category) Axis Labels Edit button. In the Axis Label dialog box in the ‘Axis label range’ textbox type this formula:

=DH!Categories

Click OK. If you were to open the Axis Label dialog box you would see that the formula has transformed into this:

=DynamicFrequencyTableAndHistogram.xlsx!Categories

Click OK on the Select Data Source dialog box.

To change the gap width to zero for the columns, click on a column and use the keyboard shortcut to open the Format Data Series dialog box: Ctrl + 1. Then change the Gap Width to zero.

Then format it as you see fit.

That should create a fully dynamic Histogram that can handle data through row 201 in column A with intervals in for the categories of 1. You can adjust any of the ranges if they are not big enough to meet the requirements of the data.

 

[gms]

There is a histogram function in Tools/Data Analysis that will automatically generate a histogram. You can create bin values or let the routine automatically select bin values. See Help for arguements & syntax.

In this example I have a Range with data values (Sand) and created a Range with bin values. It creates a histogram on a new sheet called "HistogramSand"This snippet will generate the histogram. You then will probably want to format it.

Sheets("HistogramData").Select
Application.Run "ATPVBAEN.XLA!Histogram", Range("Sand"), "HistogramSand", Range("BinFreq"), False, False, True, False