All geek questions in one place

Creating an array of box sizes in Excel

The FREQUENCY function in Microsoft Excel is useful for creating histograms. The first parameter is an array of data values ​​(for example, A1: A100). The second is an array of box sizes. I want to generate this array of three parameters:

  • Minimum Data Value: MIN (A1: A100)
  • Maximum Data Value: MAX (A1: A100)
  • Hopper Size: Permanent

That is, the formula / function should return a list of desired values ​​instead of manually placing them in cells.

eg. Consider the minimum value of 10, the maximum value of 100 and the size of the hopper 20. This will give an array of the following hopper sizes: 10, 30, 50, 70, 90

How can i do this? Thank.

+2
source share
1 answer

If you want this array generated by the formula try this

=(ROW(INDIRECT("1:"&CEILING((MAX(A1:A100)-MIN(A1:A100))/B1,1)))-1)*B1+MIN(A1:A100)

where B1 is the constant of your hopper size, for example. in the frequency function, which will

=FREQUENCY(A1:A100,(ROW(INDIRECT("1:"&CEILING((MAX(A1:A100)-MIN(A1:A100))/B1,1)))-1)*B1+MIN(A1:A100))

Confirmed by CTRL + SHIFT + ENTER

+2
source

Source: https://habr.com/ru/post/1674162/

More articles:


All Articles