On 13 Dec., 15:04, jkrons <j...@knord.dk> wrote:
> 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
|
