Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Bin Ranges in Histograms

1. Join Date
14th January 2006
Posts
4

## Bin Ranges in Histograms

I'm trying to create a histogram using the Analysis Tool Pak, however when I create the column for my Bin Range, the output isn't right, it as if all of my data is skewed by one bin. For example, using a bin size of 10, data points 23,25.. etc show up in the 30+ bin rather than the 20+ bin. I'm very confused, all of the on-line help I look at says I'm doing it correctly, but I still seem to be doing something fundamentally wrong.

Excel Video Tutorials / Excel Dashboards Reports

2. ## Re: Bin Ranges in Histograms

Hi,

Can you post an example workbook?

3. Join Date
14th January 2006
Posts
4

## Re: Bin Ranges in Histograms

I'll see what I can do, however, I did notice last night, that what's happening is that the Bin number becomes the upper limit rather than the lower limit. For example if I enter bin data of 20,30,40. The histogram is returing the data anthing less than 20 goes in the 20's bin, and anything between 20-30 goes in the 30's bin.

here is the data I'm trying to crunch. (just example data)
12 20
23 30
23 40
24 50
25 60
32 70
33
34
34
35
42
43
43
43
43
45
45
46
53
53
54
56
63
64
76
76

And here is the data that I get in my histogram

Bin Frequency
20 1
30 4
40 5
50 8
60 4
70 2
More 2

If you look at the data (which I sorted) you can see that 12 went inot the 20 bin and 23,23,24,25 all went into the 30 bin.

Do I need to establish a lower and upper range for the Bin? How do I do that?

Excel Video Tutorials / Excel Dashboards Reports

4. ## Re: Bin Ranges in Histograms

This is how the data is allocated, which is as expected.
VB:
```>=  <   N   Values
0   20  1 12
20  30  4 23,23,24,25
30  40  5 32,33,34,34,35
40  50  8 42,43,43,43,43,43,45,46
50  60  4 53,53,54,56
60  70  2 63,64
70  *   2 76,76

```

5. Join Date
14th January 2006
Posts
4

## Re: Bin Ranges in Histograms

I'm not familiar with VBA, is that different from the Analysis tool pak histogram add-in? Also, there is supposed to be away to do a frequency array, without using the Analysis tool pak, but I oculdn't get that to work either. I've got a reasonable "work-around" for the issue I'm haing, but I'd really like to know why the Bins correlate the data the way do.

Excel Video Tutorials / Excel Dashboards Reports

6. ## Re: Bin Ranges in Histograms

That VBA is just the formatting I used in order for the values to line up.

That is how the values you provided would be are in fact are being grouped by the histogram addin.

What are you expecting?

7. Join Date
14th January 2006
Posts
4

## Re: Bin Ranges in Histograms

Well, if you look at the data I provided the data comes up skewed by one. Everything less tha 20 is in the 20 bin, where I'm expecting everything between 20-30 to be in the 20 bin. My data in the histogram looks likes this in my workbook:

Bin Frequency
20 1
30 4
40 5
50 8
60 4
70 2
More 2

I want it too look like this:

Bin Frequency
10 1
20 4
30 5
40 8
50 4
60 2
70 2

Excel Video Tutorials / Excel Dashboards Reports

8. ## Re: Bin Ranges in Histograms

Here is the description of Bins from the help file
A number is counted in a particular bin if it is equal to or less than the bin number down to the last bin. All values below the first bin value are counted together, as are the values above the last bin value.
Attached is a histogram with a formula used to tally values within bins.

#### Thread Information

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno