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

Back to archive home

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

Use the Countif function:

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

3 1 2 3

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

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

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

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

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

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

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

> 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

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

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.

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.

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.