Frequency Distribution Formulas

[zanshin777]

I don't understand the formula (especially INDIRECT part) which create 10 frequency range from the data on the G Column.

=MIN(data)+(ROW(INDIRECT("1:10"))*(MAX(data)-MIN(data)+1)/10)-1

[shg]

Suppose you have data ranging from some min to some max that you want to distrubute in 10 equal-size bins. What number should be the top of the first bin? How would you calculate it?

[zanshin777]

Suppose you have data ranging from some min to some max that you want to distrubute in 10 equal-size bins. What number should be the top of the first bin? How would you calculate it?

I don't know.

[zanshin777]

The formula on G column looks like unfamiliar to me. ">="&D2 is used rather than ">=&D2"

=COUNTIFS(Grades,">="&D2,Grades,"<="&E2)

[zanshin777]

Normally we don't put " " around mathematical signs like "+, - , =, >=, <=" but here we've put. Why?

[shg]

">=&D2" matches the literal string ">=&D2"

It doesn't contain a cell reference at all.

[zanshin777]

">=&D2" matches the literal string ">=&D2"

It doesn't contain a cell reference at all.

What about >=&D2 ?

[shg]

That would be invalid syntax. The criteria expression needs to evaluate to a string.

[shg]

Because in a CountIf function, they are not operators -- they are used to build a string that is the right half of a Boolean expression evaluated for each element of the range.

In =if(a1 > 1, "a", "b"), > is an operator

In =countif(a1:a10, ">1"), ">1" is a string, and a1 & ">1" is evaluated as a Boolean expression, then a2 & ">1", then ...

In a Boolean expression, you can have a value:

if a then

or an expression that included two or more values separated by a comparison operator

if a > b then

>1 is not an expression; there's nothing on the left.

That's a gross simplification of an expression, but correct as far as it goes.

[zanshin777]

I don't understand the formula (especially INDIRECT part) which create 10 frequency range from the data on the G Column.

=MIN(data)+(ROW(INDIRECT("1:10"))*(MAX(data)-MIN(data)+1)/10)-1

Could anybody explain how the formula works?

[ChemistB]

Okay, I'll give it a try. The formula is dividing your range up into 10 even segments. This is done by dividing the quantity of Max value - min value plus 1 by 10 to give you the "size" of each division. Then this value is added to the starting point which is your minimum data point (minus 1 so it ends up exactly at the max value and not max value +1).

So, this formula could have been written
=MIN(data)+(MAX(data)-MIN(data)+1)/10-1
Then in G3 copied down
=G2+(MAX(data)-MIN(data)+1)/10

It was meant to be entered as an Arrayed formula in G2:G11.
Select G2:G11 and then, in the formula bar, input and enter as an array

=MIN(data)+(ROW(INDIRECT("1:10"))*(MAX(data)-MIN(data)+1)/10)-1

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

About The use of ROW(INDIRECT("1:10") )
1:10 is seen as text in this formula until INDIRECT tells Excel that it refers to a Range (rows 1 to 10)
Then ROW(1:10) returns an array of {1,2,3,4,5,6,7,8,9,10} and populates the cells G2:G11 sequentially with these values. So, for example, in G8, the formula would simplify to

=MIN(data)+(7*(MAX(data)-MIN(data)+1)/10)-1

The Frequency Column (H) should also be entered as an array in H2:H11

=FREQUENCY(data,G2:G11)
Questions?

[zanshin777]

=MIN(data)+(ROW(INDIRECT("1:10"))*(MAX(data)-MIN(data)+1)/10)-1

By this formula the frequency range is added to the minimum value then 1 is subtracted on the first row. What does happen on the second row?

[shg]

They are manual entries.

[ChemistB]

As SHG said, in your worksheet, they are manual entries. if you reread post #11, it tells how to enter that formula as an array and also how it calculates in each cell (I used G8 as an example).

I believe, it was meant to be entered as an array, otherwise, there's no sense in using the INDIRECT("1:10")