Routine for Normalized Relative Frequency Histogram

[Equanimity]

Hello Everyone!

I’m aware of a few types of histograms.

I'm aware that Excel supports a standard "frequency histogram" via DATA > DATA ANALYSIS > HISTOGRAM. The is the simplest kind, in that the height of the bar is the # of outcomes that fall within the “bin”.

A slightly more sophisticated type of histogram is called a “Relative Frequency Histogram”, which tells us the percentage of outcomes that fall within each bin.

What I'm looking to do is build a "Normalized Relative Frequency Histogram", which does not grow taller or flatter if we change the bin width.

If we define "y" as the height of the bar, then the Normalized Relative Frequency Histogram is:

y = (# of observations in each bin) / ((bin width) * (number of realizations))

The key idea behind the Normalized Relative Frequency Histogram is that the AREA under the histogram is equal to 1 (which is important because it's consistent with the definition of probability).

Mathematically, it looks like:

area = (1/number of realizations)*(number of realizations) = 1

This type of histogram is really important in cases where the bin width shrinks close to 0 and the number of observations goes to infinity.

When taken to the limit, this type of histogram leads to the notion of Continuous Probability Density.

Does anyone know how to tackle this with some VBA code?

Thanks!
Sarah

 

[shg]

How about just a formula?

	A​	B​	C​	D​	E​
1​	Value​	​	Bin​	Fraction​	​
2​	1​		0​	0.08140​	D2:D11: {=FREQUENCY(A2:A87, C2:C11) / ROWS(A2:A87)}
3​	8​		1​	0.06977​
4​	8​		2​	0.12791​
5​	9​		3​	0.12791​
6​	9​		4​	0.08140​
7​	1​		5​	0.03488​
8​	0​		6​	0.12791​
9​	0​		7​	0.08140​
10​	7​		8​	0.11628​
11​	9​		9​	0.15116​
12​	2​		Total​	1.00000​	D12: =SUM(D2:D11)
13​	0​
14​	2​
15​	7​
16​	8​
17​	2​
18​	9​
19​	1​
20​	6​
21​	...​

 

[Equanimity]

Hi shg,

Thank you for your quick reply! That solution definitely works.

Do you know if it's possible to have some sort of subroutine where you can define the following:

a) the data itself
b) the bins

And then the subroutine builds the histogram and then normalizes it to equal 1 (so that I can avoid building formulas each time I need to analyze a set of data)?

Thanks!
Sarah

 

[shg]

Where is the data? How would one decide how many bins? What should the result look like?

 

[Equanimity]

Hi shg,

To answer your questions:

1) The data doesn't exist right now, per se. I plan to use this type of histogram to analyze financial data. The data could contain as few as 2 observations, or as many as several hundred thousand observations.
2) Ideally, the subroutine would "know" how to create the bins such that the area under the curve is equal to 1.
3) The result should just be such that the area of the histogram is "normalized" and equal to 1.

The original solution that you put forth makes perfect sense. I was just hoping to 'automate' it a little bit, such that I can:

a) have a template of some kind
b) 'drop in' whatever data I want to analyze
c) click a button and have the subroutine create a normalized histogram with area equal to 1

I hope I've answered your questions adequately...

Thank you again for your help!
-Sarah

 

[shg]

2) Ideally, the subroutine would "know" how to create the bins such that the area under the curve is equal to 1.
3) The result should just be such that the area of the histogram is "normalized" and equal to 1.

The number and size of bins has to be decided independently; any histogram can be normalized to a sum of 1 by dividing each count by the total number of samples.

 

[Equanimity]

Hi shg,

Would it be possible to incorporate the number and size of bins as "inputs" to a subroutine and/or function?

Thanks,
Sarah

 

[shg]

The FREQUENCY function allows you to select the data and the bins. I'm missing what additional functionality a macro might provide.