I have a range of data that is listed in a column. I need to plot this data
in terms of numbers of observations. For instance, the column reading down
has the following points:

0.77
0.68
0.68
0.65
0.65
0.65
0.60 and so on...

The values are sorted from highest to lowest and there are 130 data points.

I need the data values on the y-axis and the number of times the value
occurs on the x-axis. Can anyone offer advice? Thank you.

Hi,

One way (eventhough not very elegant!) is as follows:

Let's say that your data are in A2:A131. Enter the following formula in B2
(and fill-in the formula down to B131).

=COUNTIF($A$2:$A$131,"="&A2)

Column B will now contain the number of occurrences of each value in Column
A (There will be redundant information when there are duplicates in Column A,
but they wouldn't hurt the graph).
Now make a plot of A2:A131 (y-axis) vs B2:B131 (x-axis)

REgards,
B. R. Ramachandran

"lost in charts" wrote:

I have a range of data that is listed in a column. I need to plot this data
in terms of numbers of observations. For instance, the column reading down
has the following points:

0.77
0.68
0.68
0.65
0.65
0.65
0.60 and so on...

The values are sorted from highest to lowest and there are 130 data points.

I need the data values on the y-axis and the number of times the value
occurs on the x-axis. Can anyone offer advice? Thank you.

Hi, thanks for your response. I did what you recommended, but all the values
in column B came out 1. Help!

"B. R.Ramachandran" wrote:

Hi,

One way (eventhough not very elegant!) is as follows:

Let's say that your data are in A2:A131. Enter the following formula in B2
(and fill-in the formula down to B131).

=COUNTIF($A$2:$A$131,"="&A2)

Column B will now contain the number of occurrences of each value in Column
A (There will be redundant information when there are duplicates in Column A,
but they wouldn't hurt the graph).
Now make a plot of A2:A131 (y-axis) vs B2:B131 (x-axis)

REgards,
B. R. Ramachandran

"lost in charts" wrote:

I have a range of data that is listed in a column. I need to plot this data
in terms of numbers of observations. For instance, the column reading down
has the following points:

0.77
0.68
0.68
0.65
0.65
0.65
0.60 and so on...

The values are sorted from highest to lowest and there are 130 data points.

I need the data values on the y-axis and the number of times the value
occurs on the x-axis. Can anyone offer advice? Thank you.

Then your numbers display a rounded value, but contain more digits of
significance, which make them different. You can instead make a
histogram (Tools menu Data Analysis) or use the Frequency function to
generate the table you need.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______


lost in charts wrote:

Hi, thanks for your response. I did what you recommended, but all the values
in column B came out 1. Help!

"B. R.Ramachandran" wrote:


Hi,

One way (eventhough not very elegant!) is as follows:

Let's say that your data are in A2:A131. Enter the following formula in B2
(and fill-in the formula down to B131).

=COUNTIF($A$2:$A$131,"="&A2)

Column B will now contain the number of occurrences of each value in Column
A (There will be redundant information when there are duplicates in Column A,
but they wouldn't hurt the graph).
Now make a plot of A2:A131 (y-axis) vs B2:B131 (x-axis)

REgards,
B. R. Ramachandran

"lost in charts" wrote:


I have a range of data that is listed in a column. I need to plot this data
in terms of numbers of observations. For instance, the column reading down
has the following points:

0.77
0.68
0.68
0.65
0.65
0.65
0.60 and so on...

The values are sorted from highest to lowest and there are 130 data points.

I need the data values on the y-axis and the number of times the value
occurs on the x-axis. Can anyone offer advice? Thank you.

Hi,

As Jon has indicated in his response, your numbers contain more significant
digits than displayed, making each different from the other (e.g., 0.432 and
0.434 both are displayed as 0.43).
If you want to stick to 2 significant digits after the decimal, do the
following.
If your data are in A2:A131, use the following formula in B2 (and fill-in
the formula down to B131)

=ROUND(A2,2)

This creates a column of your numbers that have been truncated to exactly
two signficant digits after the decimal.

Now use the COUNTIF formula in C2 (and fill-down the formula to C131)

=COUNTIF($B$2:$B$131,B2).

Make an XY-scatter plot of columns B vs C.

Regards,
B. R. Ramachandran

"lost in charts" wrote:

Hi, thanks for your response. I did what you recommended, but all the values
in column B came out 1. Help!

"B. R.Ramachandran" wrote:

Hi,

One way (eventhough not very elegant!) is as follows:

Let's say that your data are in A2:A131. Enter the following formula in B2
(and fill-in the formula down to B131).

=COUNTIF($A$2:$A$131,"="&A2)

Column B will now contain the number of occurrences of each value in Column
A (There will be redundant information when there are duplicates in Column A,
but they wouldn't hurt the graph).
Now make a plot of A2:A131 (y-axis) vs B2:B131 (x-axis)

REgards,
B. R. Ramachandran

"lost in charts" wrote:

I have a range of data that is listed in a column. I need to plot this data
in terms of numbers of observations. For instance, the column reading down
has the following points:

0.77
0.68
0.68
0.65
0.65
0.65
0.60 and so on...

The values are sorted from highest to lowest and there are 130 data points.

I need the data values on the y-axis and the number of times the value
occurs on the x-axis. Can anyone offer advice? Thank you.

Hello,

The chart worked out well. Thank you very much for your help. However, now
my boss wants to change things a little. He wants to group the data points
in a series so that on the y-axis, the labels will read:

0.15 - 0.10
0.10 - 0.05
0.05 - 0.00, etc.

Then he wants me to use a column chart instead.

How do I get the data points to group in a series, then also the number of
occurrences in that series (which is what we did before)? Thanks again!

-Kit


"B. R.Ramachandran" wrote:

Hi,

As Jon has indicated in his response, your numbers contain more significant
digits than displayed, making each different from the other (e.g., 0.432 and
0.434 both are displayed as 0.43).
If you want to stick to 2 significant digits after the decimal, do the
following.
If your data are in A2:A131, use the following formula in B2 (and fill-in
the formula down to B131)

=ROUND(A2,2)

This creates a column of your numbers that have been truncated to exactly
two signficant digits after the decimal.

Now use the COUNTIF formula in C2 (and fill-down the formula to C131)

=COUNTIF($B$2:$B$131,B2).

Make an XY-scatter plot of columns B vs C.

Regards,
B. R. Ramachandran

"lost in charts" wrote:

Hi, thanks for your response. I did what you recommended, but all the values
in column B came out 1. Help!

"B. R.Ramachandran" wrote:

Hi,

One way (eventhough not very elegant!) is as follows:

Let's say that your data are in A2:A131. Enter the following formula in B2
(and fill-in the formula down to B131).

=COUNTIF($A$2:$A$131,"="&A2)

Column B will now contain the number of occurrences of each value in Column
A (There will be redundant information when there are duplicates in Column A,
but they wouldn't hurt the graph).
Now make a plot of A2:A131 (y-axis) vs B2:B131 (x-axis)

REgards,
B. R. Ramachandran

"lost in charts" wrote:

I have a range of data that is listed in a column. I need to plot this data
in terms of numbers of observations. For instance, the column reading down
has the following points:

0.77
0.68
0.68
0.65
0.65
0.65
0.60 and so on...

The values are sorted from highest to lowest and there are 130 data points.

I need the data values on the y-axis and the number of times the value
occurs on the x-axis. Can anyone offer advice? Thank you.

Hi,

If you have 'Data Analysis" installed in Excel, you can easily do this by
making a histogram. Let's suppose that your data are in A2:A131.

In B2:B21, enter numbers, 0.05, 0.10, 0.15, ....., 1.00. Then,
"Tools" -- "Data Analysis"/ Select Histogram
Input Range $A$2:$A$131
Bin Range $B$2:$B$21
Output Range $C$2
Chck 'Chart Output' button at the bottom,
"OK"

If you don't have "Data Analysis" installed, do the following.

In B2:B22, enter 0, 0.05, 0.10, 0.15, ......., 1.00.
In C3, enter the formula, =SUMPRODUCT(($A$2:$A$131B2)*($A$2:$A$131<=B3))
Fill-down the formula to C22.
Make a column chart of C3:C22 vs B3:B22.

Hope this helps,
Regards,
B. R. Ramachandran




"lost in charts" wrote:

Hello,

The chart worked out well. Thank you very much for your help. However, now
my boss wants to change things a little. He wants to group the data points
in a series so that on the y-axis, the labels will read:

0.15 - 0.10
0.10 - 0.05
0.05 - 0.00, etc.

Then he wants me to use a column chart instead.

How do I get the data points to group in a series, then also the number of
occurrences in that series (which is what we did before)? Thanks again!

-Kit


"B. R.Ramachandran" wrote:

Hi,

As Jon has indicated in his response, your numbers contain more significant
digits than displayed, making each different from the other (e.g., 0.432 and
0.434 both are displayed as 0.43).
If you want to stick to 2 significant digits after the decimal, do the
following.
If your data are in A2:A131, use the following formula in B2 (and fill-in
the formula down to B131)

=ROUND(A2,2)

This creates a column of your numbers that have been truncated to exactly
two signficant digits after the decimal.

Now use the COUNTIF formula in C2 (and fill-down the formula to C131)

=COUNTIF($B$2:$B$131,B2).

Make an XY-scatter plot of columns B vs C.

Regards,
B. R. Ramachandran

"lost in charts" wrote:

Hi, thanks for your response. I did what you recommended, but all the values
in column B came out 1. Help!

"B. R.Ramachandran" wrote:

Hi,

One way (eventhough not very elegant!) is as follows:

Let's say that your data are in A2:A131. Enter the following formula in B2
(and fill-in the formula down to B131).

=COUNTIF($A$2:$A$131,"="&A2)

Column B will now contain the number of occurrences of each value in Column
A (There will be redundant information when there are duplicates in Column A,
but they wouldn't hurt the graph).
Now make a plot of A2:A131 (y-axis) vs B2:B131 (x-axis)

REgards,
B. R. Ramachandran

"lost in charts" wrote:

I have a range of data that is listed in a column. I need to plot this data
in terms of numbers of observations. For instance, the column reading down
has the following points:

0.77
0.68
0.68
0.65
0.65
0.65
0.60 and so on...

The values are sorted from highest to lowest and there are 130 data points.

I need the data values on the y-axis and the number of times the value
occurs on the x-axis. Can anyone offer advice? Thank you.