Average of numbers in column between to other numbers

[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!

[Peo Sjoblom]

One way

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

entered with ctrl + shift & enter

--
Regards,

Peo Sjoblom


"Ditandhischeese" <Ditandhischeese@discussions.microsoft.com> wrote in
message news:07E82578-9125-4C99-90D2-A4F159FBAE13@microsoft.com...
> 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!

[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



"Ditandhischeese" <Ditandhischeese@discussions.microsoft.com> wrote in
message news:07E82578-9125-4C99-90D2-A4F159FBAE13@microsoft.com...
> 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!