# Calculate average in intervals

J

#### jkrons

I use FREQUENCY to calculate the number of ocurrences of numbers,
within certain intervals, like

=FREQUENCY(A1:A1000,B1:B10) as an array formula where A is the numbers
I want couted and B1:B10 is my intervals. This is easy enough,, but
now I want to calculate the average value of the numbers within each
interval.

I have tried something like

=SUMIF(A1:A1000,"<="&F2,A1:A1000)/COUNTIF(A1:A1000,"<="&B2) and this
works, if I just want to get the average of vaues below something,
But in this case the value should be BETWEEN two limits. I have tried
something like this:

=SUMIF(\$A\$1:\$A\$1000,AND(\$A\$1:\$A\$1000<=F2,\$A\$1:\$A\$1000>F1),\$A\$1:\$A
\$1000)

And that definately doesn't work.

Any suggestions on how to achieve what I want

J

#### jkrons

I use FREQUENCY to calculate the number of ocurrences of numbers,
within certain intervals, like

=FREQUENCY(A1:A1000,B1:B10) as an array formula where A is the numbers
I want couted and B1:B10 is my intervals. This is easy enough,, but
now I want to calculate the average value of the numbers within each
interval.

I have tried something like

=SUMIF(A1:A1000,"<="&F2,A1:A1000)/COUNTIF(A1:A1000,"<="&B2) and this
works, if I just want to get the average of vaues below something,
But in this case the value should be BETWEEN two limits. I have tried
something like this:

=SUMIF(\$A\$1:\$A\$1000,AND(\$A\$1:\$A\$1000<=F2,\$A\$1:\$A\$1000>F1),\$A\$1:\$A
\$1000)

And that definately doesn't work.

Any suggestions on how to achieve what I want

I solved it by using a normal FREQUENCY formula in column F:
=FREQUENCY(A1:A4000,E2:E11) with A2:A11 as my interval vector. Then I
calculated the average per interval using this formular in G2 and
copying it down:

=(SUMIF(\$A\$1:\$A\$4000,"<="&E2)-SUMIF(\$A\$1:\$A\$4000,"<="&E1))/(COUNTIF(\$A
\$1:\$A\$4000,"<="&E2)-SUM(\$F\$1:F1))

but I'm still interested in a more sexy solution if possible.,

Regards
Jan