+ Reply to Thread
Results 1 to 6 of 6

Dynamic Histogram

  1. #1
    Registered User
    Join Date
    07-02-2014
    Location
    Ohio
    MS-Off Ver
    2010
    Posts
    33

    Dynamic Histogram

    Hello

    I'm trying to come up with a histogram chart that adjusts Bin Size based on user input. I've come up with the formulas for Bins and Frequency, but am struggling when it comes to charting the data to dynamically update when the user changes Bin Size. If anyone knows how to make the x-axis dynamically adjust with the number of Bins please help me out.

    Thank you
    ~Bricks
    Attached Images Attached Images
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    07-02-2014
    Location
    Ohio
    MS-Off Ver
    2010
    Posts
    33

    Re: Dynamic Histogram

    This comes a little closer but drops off the last bin if it does not end right on Max([data]).
    Attached Files Attached Files

  3. #3
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,677

    Re: Dynamic Histogram

    Why have the user enter the bin size rather than calculate the bin size based on the user-entered bin count and max and min data values?
    Attached Files Attached Files
    Entia non sunt multiplicanda sine necessitate

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,806

    Re: Dynamic Histogram

    Hi IcyBricks,

    A Pivot Table can do this without any formulas. See the attached. You will simply need to change the GROUP bin size.

    Read this site:
    http://www.excelfunctions.net/group-pivot-table.html
    Attached Files Attached Files
    Last edited by MarvinP; 07-02-2014 at 05:36 PM.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  5. #5
    Registered User
    Join Date
    07-02-2014
    Location
    Ohio
    MS-Off Ver
    2010
    Posts
    33

    Re: Dynamic Histogram

    @MarvinP Thanks for the tip. I've never tried to group anything other than date/time or categories in pivot tables. Very cool! I was bummed to find you could not feed the "By" parameter a cell address but I wrote out the VBA for that:
    Please Login or Register  to view this content.
    @shg Point taken about user picking number of bins. In my code I let the user do either BinSize or #ofBins.

    If anyone has a non-VBA solution that does everything the attached workbook does I'd love to hear it.
    Attached Files Attached Files

  6. #6
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,079

    Re: Dynamic Histogram

    This uses formula and named ranges to react to Bin Size. Same appraoch can be used for #Bins.

    You might want to tidy it up so the last bin does not go beyond max data value.
    Attached Files Attached Files
    Cheers
    Andy
    www.andypope.info

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Frequency with subtotal function formula for dynamic histogram
    By 5150 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 11-12-2013, 08:37 PM
  2. [SOLVED] Dynamic Histogram
    By cas8100 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 02-26-2013, 01:20 AM
  3. Dynamic Histogram
    By cas8100 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 02-26-2013, 12:49 AM
  4. Coding Up Histogram with dynamic range
    By mashoutposse in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 04-03-2010, 12:43 PM
  5. [SOLVED] How to Histogram w/o raw data but histogram Table on Excel
    By 2005 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 02-01-2006, 02:50 AM

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1