Histogram of select data

Posted by John H on March 06, 2001 3:14 PM

We measure 50 values each day and enter them into excel. Weekly I create histograms for the past 3 months for each. I want to set this up so others can click and go. I wrote a macro to speed up the histograms, but the input range is fixed. My problem is a way to select records out of my table for just the last 90 days.

The histogram function does not recognize the autofilter like a chart does, so the easy way was out. Instead, I indexed the data table and used a grid of VLOOKUP to pull the records over to the range referenced by the histogram macro. This works, but painfully slow. Any ideas?

Posted by Mark W. on March 06, 2001 3:31 PM

Did you consider using OFFSET() instead of VLOOKUP()?

Posted by John H on March 07, 2001 7:38 AM

Thanks - OFFSET() is several magnitudes faster than VLOOKUP(). I also used a CSE to get around the problem with numbers vs. characters in my array for standard deviation and histogram formulas.

Now if only the histogram analysis would go faster, they would think I am a minor diety! I guess I could tell them it was you... nah.