1. 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. 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. 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. 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. 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. 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. Re: Histograms - Dynamic (Excel97SR2)

> the FREQUENCY worksheet function.

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

8. 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
•