Solved: Distribution Graphs

[khalid79m]

I need some help, serious help.

I have been asked to produce some graphs to show

the Distribution away from the average for longest silence on a call.

Can anyone help I have attached a dummy worksheet, with dummy data:help

[MWE]

I am not sure what you want. I assume by "distribution" you mean distribution in the statistical sense -- sometimes called a histogram -- where frequency or # of occurances is plotted vs sample values. I created histograms for silence and call length data in your spreadsheet (attached). Is this anything like what you want?

[khalid79m]

Thats exactly it but how do I do it in excekl ?

[Bob Phillips]

You need to create a results table, and chart that.

Build a list of intervals to be plotted, and use the FREQUENCY function to calculate the data over those intervals.

[MWE]

Thats exactly it but how do I do it in excekl ?the histograms provided were generated in MiniTab, but there are several ways to generate a histogram in Excel.

First, a few words about histograms:
Assume you have N values and want to generate a histogram. You compute the max and min of the data series and break that range into a number of cells or buckets. Let assume that the low value is 10 and the high value is 110 and the number of values is 150. A rough rule of thumb is that the number of cells should be = sqrt(# values). So if you had 150 values, you would want 12 cells or buckets, each cell has a width of approx (110-10)/12 = 8.33. The first cell starts at 10 and finishes at 18.33. The second cell starts at 18.33 and finishes at 26.67 and so on. You then work your way through the original data and for each data point determine which cell it belongs to and add "1" to the count for that cell. When you are done, you have a freq distribution for the original data. It is a simple counting process.

Excel has the capability to generate histograms without any VBA if you have the Analysis Pack installed:
go to Tools Menu and see if Data Analysis is listed (normally at bottom)
if not, go to Add Ins and check the analysis pack boxes
follow the instructions to load the analysis pack
then go to Tools | Data Analysis and click on Histogram
follow the instructions. Generally you need to enter data in only two boxes: the input data box (leave bin inuput blank) and the Output Range box (requires just a single cell value -- see the attachment).
Excel will compute the number of buckets or bins automatically and generate a matrix of results using the cell value you entered for Output Range as the upper left corner of the 2 col by n row matrix. The matrix has cell center points in the first col and frequency in the second.
Then plot the matrix as you want.The attached was done using the built-in Excel capability. See sheet1B. The histogram for silence data is at the top; the histogram for call length data is around row 75. I turned on the Macro Reader for the 2nd histogram so you can see what would be required to do this using VBA and the built in capability of Excel.

You can also generate a histogram using custom VBA code. I have done that before -- not very hard.

[MWE]

You need to create a results table, and chart that.

Build a list of intervals to be plotted, and use the FREQUENCY function to calculate the data over those intervals.Yes, this is another approach. You have to decide what the intervals are and still should use # of cells ~= sqrt(N). There are other formulas for calculating the # of cells, but SQRT(N) is pretty good and very easy to remember.

[khalid79m]

Thanks for all the advice