Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 8 of 8

Thread: Bin Ranges in Histograms

  1. #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. #2
    Join Date
    7th March 2003
    Location
    Essex, England
    Posts
    11,314

    Re: Bin Ranges in Histograms

    Hi,

    Can you post an example workbook?

    Cheers
    Andy


  3. #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. #4
    Join Date
    7th March 2003
    Location
    Essex, England
    Posts
    11,314

    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 
    
    

    Cheers
    Andy


  5. #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. #6
    Join Date
    7th March 2003
    Location
    Essex, England
    Posts
    11,314

    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?

    Cheers
    Andy


  7. #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. #8
    Join Date
    7th March 2003
    Location
    Essex, England
    Posts
    11,314

    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.
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Cheers
    Andy


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Replies: 5
    Last Post: October 25th, 2007, 23:28
  2. 3 Histograms: A, B, A+b
    By sombrancelha in forum EXCEL HELP
    Replies: 3
    Last Post: May 11th, 2007, 04:12
  3. Replies: 1
    Last Post: May 9th, 2007, 03:08
  4. Copy Ranges & Add Them To Other Ranges
    By Babuanjal in forum EXCEL HELP
    Replies: 10
    Last Post: November 2nd, 2006, 18:06
  5. Histograms via Scatter Plots and Error Bars
    By Vertex42 in forum OPEN SOURCE: Hey! That is Cool!
    Replies: 0
    Last Post: June 12th, 2004, 15:31

Bookmarks

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