Results 1 to 8 of 8
  1. #1
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Histograms - Dynamic (Excel97SR2)

    Excel has the ability to plot a Histogram (Tools, Data Analysis), but it seems to be quite limited in utility as the histogram is not plotted dynamically.

    I am acustomed to creating a chart, and watching the graphics change as the data in the source ranges change.

    Is anyone else out there lamenting the static nature of Histograms? It seems to me to be a particularly useless restriction.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Histograms - Dynamic (Excel97SR2)

    If you want a dynamic histogram, look up the FREQUENCY worksheet function. You can use this to create the frequency distribution, then create a column chart based on the frequency distribution. The frequencies will be dynamic, but since you must provide the set of bins yourself, they will not be dynamic.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Histograms - Dynamic (Excel97SR2)

    In addtion to the frequency function suggested by Hans, you can use SUMIF/COUNTIF functions to fill the info on the histogram. Your "bins" can be dynamic if you create formulas for them.

    If you need more details, you will have to elaborate on what you have and what you need.

    Steve

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Histograms - Dynamic (Excel97SR2)

    > If you need more details, you will have to elaborate on what you have and what you need.

    Steve, thanks for the tips. I've used SumIf and CountIf before, but not as Array Functions.

    What I have is a Client's University who is wondering why I'm not a keen fan of Histogram. My basic answer is "If it isn't dynamic, I consider it to have limited utility".

    So my prime query is along my usual lines of "Am I imagining this, or is this really how MSoft implemented it".

    Given Hans's demonstration of what can be done, it seems a shame that MSoft decided not to implement it for Histogram.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Histograms - Dynamic (Excel97SR2)

    <hr>I've used SumIf and CountIf before, but not as Array Functions.<hr>

    I don't understand where the array function comes in or your comment.

    Could you elaborate on what you need? Excel histograms are dynamic if the dataranges are dynamic or use formulas. If you explain what you are after, I think we can provide more details

    Steve

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Histograms - Dynamic (Excel97SR2)

    The Histogram function as I used it (Excel97/SR2) produced fixed values as a result, not dynamic. I could not get the results to be dynamic, based on changes in either the data range or the bins, or both.

    I entered Array formulae with Frequency because I wanted an array of results.

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Histograms - Dynamic (Excel97SR2)

    > the FREQUENCY worksheet function.

    Thanks Hans. I did this, and had a lot of fun with Array Functions.

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Histograms - Dynamic (Excel97SR2)

    It is not really a function, it is a wizard. It is more akin to the "create pivot table" which is also not live. The pivot table does have a "refresh" for changed data, but you could create a macro to "refresh" the histogram. You could even run it on a "change Event"
    Something like this (change ranges and options as desired)

    <pre>Option Explicit
    Sub RefreshHistogram()
    Application.Run "ATPVBAEN.XLA!Histogram", _
    ActiveSheet.Range("$A$1:$A$26"), _
    ActiveSheet.Range("$F$11"), _
    ActiveSheet.Range("$C$1:$C$6"), _
    False, False, False, False
    End Sub</pre>


    Steve

Posting Permissions

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