Chart and statistics

When you plot population data, you have to group the data into "bins". For example, suppose to have data for how much people weigh. Depending on the size of the sample, you will have to group the data. Suppose you group the data by people weighing 80-85 (lbs), then 85-90, and so on, so each bin has a range of 5 pounds. So each point on the plot is the number of people who fall into that range.

Are the data points in your PDF file all the samples, or have they already been grouped into bins? For example, for the value 10, does that mean that you have a single member of the population with a value of 10, or that you have 10 members of the population within some range of values (a bin)?

If the latter, then all you have to do is plot your data on a line chart.

Would be helpful if you provided additional information about what data you are dealing with.

You were on the right track with creating a histogram. If you create the histogram data and chart, you can simply change the format of the bar chart to a line graph. To create a histogram, add a column with your bin breakpoints. I have attached an example showing a few thousand random numbers (so you don't get a bell curve), and a column of bins using an interval of 0.01. The columns and graph to the right were generated by Excel. Then I modified the graph to be a line graph.

As far as the bin size, there may be some heuristics for this but my statistics background is rather weak so I can't give you a method for selecting a bin size. Personally I would iterate different sizes to get one that is as small an interval as possible that still yields a bell-shaped curve. What is the range of your data?

Edit: See for example this site

Forgot attachment

A quick way is to calculate the Standard Deviation of your data and the mean

=stdev(data)
=average(data)

now creat a new row

c1: =averagecell-6*stdevcell
c2: = c1+stdevcell
..
c13

now in d1 type =frequency(data,c1:c12)
now highlight d1:d13 and press f2, now CTRL-SHIFT-ENTER

you now have your data for the histogram!

see if that helps!

Originally Posted by joelhuang

Wow I'm so eager to try it out! I'm on my way to office now my sample data ranges from $2 to $650,000.. Do I manually decide the bin range? Or do i use a formula for it?

Which solution are you responding to?

For mine, using the built-in capability to produce histograms, you create the bin range manually. If you look at the link I provided you can get some idea of the thinking in the field about selecting bin ranges. You might want to experiment because the nature of the data will make a difference.

For squiggler47's, the bin range is the standard deviation. However, that solution appears to plot only from the mean ± 6 standard deviations. If you have data outside 6 standard deviations then it won't be included.

Here is 3 examples of different methods of creating histogram bins, take your pick!

For squiggler47's, the bin range is the standard deviation. However, that solution appears to plot only from the mean ± 6 standard deviations. If you have data outside 6 standard deviations then it won't be included.

the six standard deviations should take care of 99.999% of all the data, and assuming a normal distribution shouldnt make much difference to the graph! if there was a large grouping at either end of the scale the standard deviation would be larger, therefore still covering the outlying data!

here is a coverage table :-


_______________________Size
StdDevs___% of data_____ 5000
±0________0%_____________0
±1________68.3%________3,413
±2________95.45%________4,772
±3________99.73%________4,987
±4________99.994%_______5,000
±5________99.99994%_____5,000
±6________99.9999998%___ 5,000
±7________99.9999999997%_5,000