MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Frequency Chart


Posted by Mark on October 22, 2001 11:56 AM

I have a column of data that I want to either make a chart or table of the frequency of the items in the column.

For example, if the column of numbers is:

1
1
1
2
3
3
4
4
4

I want a table that reads:

# Frequency
1 3
2 1
3 2
4 3

Or a chart that charts the # and the frequency.


Thanks!!!!


Posted by EDDIE G on October 22, 2001 12:27 PM

Use the Countif function:

=Countif(A1:A15,"1") will tell you how many "1's" are in cells A1 through A15

Posted by Juan Pablo on October 22, 2001 12:38 PM

Use Pivot Tables (NT)

3 1 2 3

Posted by Mark W. on October 23, 2001 6:46 AM

Use the histogram tool which is part of the
Data Analysis ToolPak. When properly installed
it will can be called using the Tools | Data Analysis...
menu command. I have a column of data that I want to either make a chart or table of the frequency of the items in the column. 3 1 2 3

Posted by Mark W. on October 23, 2001 6:47 AM

Use the histogram tool which is part of the
Data Analysis ToolPak. When properly installed
it will can be called using the Tools | Data Analysis...
menu command. I have a column of data that I want to either make a chart or table of the frequency of the items in the column. 3 1 2 3

Posted by Mark on October 23, 2001 12:01 PM

Thanks for your help. Is there any way to use the Histogram tool but only count the frequency of the numbers that actually appear in the first column of numbers? An example: if the column contains
1
4
4
6
6
7
7
7

Have the frequency chart look like this:
1 - 1
4 - 2
6 - 2
7 - 3

I don't want it to list a number unless it appears in the column to be counted. An example of what I DO NOT want is:

1 - 1
2 - 0
3 - 0
4 - 2
5 - 0
6 - 2
7 - 3

Many thanks!! Use the histogram tool which is part of the : I want a table that reads


Posted by Mark W. on October 24, 2001 5:32 AM

> Is there any way to use the Histogram tool but
> only count the frequency of the numbers that
> actually appear in the first column of numbers?

Yes, you could enter a list of unique values,
{1;4;6;7}, into a cell range and list that range
as "Bin Range" on the Histogram dialog.

You could also use Excel's FREQUENCY() worksheet
function. With your values in cell range A1:A8,
and your bin values, {1;4;6;7}, in cell range
C1:C4, you could enter the array formula,
{=FREQUENCY(A1:A8,C1:C4)} into cells D1:D4 to
produce your results.

Note: Array formulas must be entered using the
Control+Shift+Enter key combination. The
outermost braces, {}, are not entered by you --
they're supplied by Excel in recognition of a
properly entered array formula. Thanks for your help. Is there any way to use the Histogram tool but only count the frequency of the numbers that actually appear in the first column of numbers? An example: if the column contains Have the frequency chart look like this: - 1 - 2 - 2 - 3 I don't want it to list a number unless it appears in the column to be counted. An example of what I DO NOT want is: - 1 - 0 - 0 - 2


Posted by Mark on October 24, 2001 11:05 AM

Thanks Mark W. One last thing. Can I do what you described but without using bin values? Meaning the formula or function I use recognizes what numbers are in the list and counts the frequncy of those numbers.

The problem I have is that I have very random but very specific numbers to be counted (frequency). I don't want to change the bin values each time a new number is added to the list. Plus the numbers to be counted do not follow a specific order, i.e. 5 10 15 20 25 30. New numbers added to the list could be 22, 11, 17 etc. These additions force me to change the bin values each time because I need the exact frequncy of each number (not a "less than this but greater that this" count).

Thanks very much.

: Have the frequency chart look like this: - 1 - 2 - 2 - 3 : I don't want it to list a number unless it appears in the column to be counted. An example of what I DO NOT want is

Posted by Mark W. on October 24, 2001 11:39 AM

No, you must provide bin values for both the
histogram tool and FREQUENCY() worksheet function.
Keep in mind that you could create a list of
unique bin values each time you revise your data
by using the Advanced AutoFilter's "Unique records
only" and "Copy to another location" options. Thanks Mark W. One last thing. Can I do what you described but without using bin values? Meaning the formula or function I use recognizes what numbers are in the list and counts the frequncy of those numbers. The problem I have is that I have very random but very specific numbers to be counted (frequency). I don't want to change the bin values each time a new number is added to the list. Plus the numbers to be counted do not follow a specific order, i.e. 5 10 15 20 25 30. New numbers added to the list could be 22, 11, 17 etc. These additions force me to change the bin values each time because I need the exact frequncy of each number (not a "less than this but greater that this" count). Thanks very much.