Archive of Mr Excel Message Board

Check out our Excel Charting Resources
Back to Charting for Excel archive index
Back to archive home



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!!!!


Re: Frequency Chart

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


Use Pivot Tables (NT)

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


Re: Frequency Chart

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


Re: Frequency Chart

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


Re: Frequency Chart

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



Re: Frequency Chart

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



Re: Frequency Chart

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


Re: Frequency Chart

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.






This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.