Average of numbers in column between to other numbers

D

Ditandhischeese

If i have a big column of numbers, and i would want to select that column and
find the averages of all the numbers in that column that are between 0 and
500, how would i type that formula? Thanks!
 
P

Peo Sjoblom

One way

=AVERAGE(IF((A1:A2000>0)*(A1:A2000<500),A1:A2000))

entered with ctrl + shift & enter
 
C

CLR

=(SUMIF(A:A,">0",A:A)-SUMIF(A:A,">500",A:A))/(COUNTIF(A:A,">0")-COUNTIF(A:A,
">500"))

All on one line, watch out for email word-wrap

Vaya con Dios,
Chuck, CABGx3
 
Top