How to use FREQUENCY function in VBA?

Posted by Ron Dionne on November 06, 2001 6:55 AM

I have several thousand data values and I'd like to
find out the distribution of these values. I'd like to
have about 100 values in the BIN range. Can anyone
show me how to do this using the FREQUENCY function
via VBA?

Thanks

Posted by Bob Umlas on November 06, 2001 7:49 AM

Assuming you have your value in the BIN range, say E1:E100, then FIRST SELECT F1:F100, and enter something like
=FREQUENCY(A1:A4000,E1:E100) but be sure to use ctrl/shift/enter.
This assumes your "several thousand data values" are in A1:A4000)

Posted by Ron Dionne on November 06, 2001 8:21 AM

Can I use VBA and arrays instead?

I recorded a macro to see the code produced:
Selection.FormulaArray = "=FREQUENCY(RC[-2]:R[1239]C[-2],RC[-1]:R[20]C[-1])"

Is there a way to use the FREQUENCY function without
resorting to the old R1C1 notation, and using VBA arraya
instead of the Ranges?