averaging frequencies

[Harvey Waxman]

I tried the following in column D copied as an array as per instructions:

=average(if($a$1:$a$100>200000,$a$1:$a$100))

where Column A contains the range of values and this is the frequency result:


B C D
1 100 2 { =average(if($a$1:$a$100>200000,$a$1:$a$100))}
2 1000 68 "
3 2000 28 "
4 5000 383 "
5 10000 229
6 20000 22
7 50000 8
8 200000 5


I got #div/0 errors in all the cells in column D.

I also tried:

=SUMIF($a$1:$a$100,"<=100")/c1 in column D entered as an array

No errors but the results were all the same and made no sense.

Clearly I misunderstand the directions.

If you choose to have no bin in the last cell (I do, just for
completeness), use (array-entered)

=AVERAGE(IF($A$1:$A$100>90,$A$1:$A$100))

where 90 is the value in your highest bin.


Make sure you take a look at Help, but in this case, the SUMIF() looks
at all the values in A1:A100, and if their value is less than or equal
to 10, sums them. That sum is divided by the value returned by Frequency
for your first bin - i.e., the number of cells with values <=10. You
could use

=SUMIF($A$1:$A$00,"<=10",$A$1:$A$100)/C1

to specify that A1:A100 is the range to be added, but the default is to
add the same range as the criterion is evaluated over.

--
Harvey Products makers of Dinghy Dogs
The Boater's Best Friend
http://www.dinghydogs.com
Ladies' handbags and accessories
http://www.deducktibles.com
Remove thefrown to email me

 

[JE McGimpsey]

Here's an example:

A1:A50: numbers 1-50 (in any order)
B1:B4 numbers 5,12,24,48
B5: =MAX(A1:A50)

C1:C5 =FREQUENCY(A1:A50,B1:B5) (array entered)

Results:

C1: 5
C2: 7
C3: 12
C4: 24
C5: 2

Then array enter these two cells and copy D2 down to D5.

D1: =AVERAGE(IF($A$1:$A$50<=B1,$A$1:$A$50))
D2: =AVERAGE(IF(($A$1:$A$50>B1)*($A$1:$A$50<=B2),$A$1:$A$50))

Results:

D1: 3
D2: 9
D3: 18.5
D4: 36.5
D5: 49.5

Alternatively, (regular) enter these two cells and copy E2 down to E5:


E1: =SUMPRODUCT(--($A$1:$A$50<=B1),$A$1:$A$50)/C1
E2: =SUMPRODUCT(--($A$1:$A$50<=B2),--($A$1:$A$50>B1),$A$1:$A$50)/C2

for the same results as D15.

 

[Harvey Waxman]

Here's an example:

A1:A50: numbers 1-50 (in any order)
B1:B4 numbers 5,12,24,48
B5: =MAX(A1:A50)

You are very patient, Thanks

I found other solutions which I can decipher equally well -) but these seem
much more efficient. I'll try to parse them out but I'll probably return with
questions. I'd feel less guilty for using your work if I were able to follow
the logic.

Thanks again

Harvey


--
Harvey Products makers of Dinghy Dogs
The Boater's Best Friend
http://www.dinghydogs.com
Ladies' handbags and accessories
http://www.deducktibles.com
Remove thefrown to email me