Bar chart using Excel FREQUENCY function

Question

In Excel 2010, I have a list of values ​​in a column A, and the cell size is specified in B1. This allows me to create histograms with N cells using this formula:

{=FREQUENCY(A:A,(ROW(INDIRECT("1:"&CEILING((MAX(A:A)-MIN(A:A))/B1,1)))-1)*B1+MIN(A:A))}

The only problem is that I need to select N cells and apply this formula to get N bins that will be used as the data source for my histogram. Can I skip this step? For instance. Can this formula be used in one cell - somewhat modified - so when it is used as a data source, it is interpreted as N cells, creating a beautiful histogram with N values?

Thank.

Here is the answer that led me to the above formula.

Answer 1

(This is quite different in approach to the macro-oriented dynamic area of ​​resizing, so I use a separate answer ...)

A dynamic histogram chart can be constructed by remembering that "named ranges" are actually called formulas, so their values ​​can be dynamic, in some cases extremely.

Let's start with the assumption that we have an arbitrary set of values ​​in column A, starting from row 1, and also that we have another cell containing the number of bins that we want in our histogram. In my book, this is E2. Therefore, we launch the name manager (on the "Formulas" tab) and create

num_bins             =Sheet1!$E$2

, ( ), : , , ? *

:

data_count           =COUNT(Sheet1!$A:$A)
data_vals            =OFFSET(Sheet1!$A$1,0,0,data_count,1)
max_val              =MAX(data_vals)
min_val              =MIN(data_vals)

, , . ? :

bin_size             =(max_val-min_val)/(num_bins)

: :

bin_array            =min_val+ROW(OFFSET(Sheet1!$A$1,0,0,num_bins-1,1))*bin_size
bin_labels           =min_val+ROW(OFFSET(Sheet1!$A$1,0,0,num_bins,1))*bin_size        
data_vals            =FREQUENCY(data_vals,bin_array)

: num_bins minus one -size bin_size. min_val, FREQUENCY() . , , , . bin_labels.

. () " " ( , ). , Series =Sheet1!freq_array. . , , "". "" " () " =Sheet1!bin_labels.

2000 =RAND()*5 5 ( , )

num_bins 10. ( RAND() , )

• ( , bin_size num_bins )

Answer 2

, , - .

, .

Dim result As Variant
Dim targetCols As Long

result = Evaluate(fmla)
With rng
  targetCols = UBound(result, 1) - LBound(result, 1) + 1
  .Resize(1, targetCols).FormulaArray = fmla
End With

- , 2- ..

EDIT: ... , , : , . , :

- :

={(ROW(OFFSET(A1,0,0,CEILING((MAX(A:A)-MIN(A:A))/B1,1)+1,1))-1)*B1}

, ,

CEILING((MAX(A:A)-MIN(A:A))/B1,1)+1

OFFSET() ( , ). ROW() ( 1 , ) . (, MIN(A:A)).

, Evaluate() VBA, .

, bin FREQUENCY(), . .

( ), Worksheet_Change . , .