can histogram function in analysis toolpak update results when input data is updated?

Question

I am using histogram and correlation functions in Analysis Toolpak and need the results to be updated as I change my input data or references. Is this possible in some way?

thanks

Answer 1

Yes, you can do this with the Frequency function. Run the Histogram tool once to set up the bins and initial frequencies. You probably want a chart, so make sure to check the Chart Output option.

Now, select the frequencies and type (for example) =FREQUENCY(D1:D41,H3:H9). Replace the first argument with the range that contains your data, and the second argument with the range of your bins. The output of Frequencyis an array, so you need to select all of your frequencies before enter the function and then use Shift+Ctrl+Enter to enter the function.

This will dynamically update the frequency counts based on the initial bins. If you want, you could also calculate the bin ranges using some formula that creates them using a suitable methodology.

You can create your own histogram chart using the same bins and frequencies. Just calculate them as above, and then create a Column chart of the frequencies with the bins as the x-axis labels. This way you don't rely on the Histogram tool at all.