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
 
Ad

Advertisements

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top