Need average of numbers that occur within range
On Wed, 09 Apr 2008 13:18:40 -0400, Ron Rosenfeld
wrote:
On Wed, 9 Apr 2008 09:49:51 -0700 (PDT), Techhead
wrote:
I need help writing a formula that calculates the average number that
occurs within a range of numbers. For example, I have a column that
contains 500 rows. Each row contains a random number between 1 and
100. I want to only extract numbers that fall between the range of
1-10 and than compute the avg number that occurs between 1-10.
Thanks,
Brian
When you write "between the range 1-10" I'm not sure if you want to include 1
and 10 or only want to include numbers that fall "between" those two values.
If the latter, then:
=(SUMIF(A1:A500,"1")-SUMIF(A1:A500,"=10"))/(COUNTIF(A1:A500,"1")-COUNTIF(A1:A500,"=10"))
If the former, then you just need to change the equalities:
=(SUMIF(A1:A500,"=1")-SUMIF(A1:A500,"10"))/(COUNTIF(A1:A500,"=1")-COUNTIF(A1:A500,"10"))
If you have Excel 2007, then:
=AVERAGEIFS(A1:A500,A1:A500,"1",A1:A500,"<10")
or, depending on what you mean by between:
=AVERAGEIFS(A1:A500,A1:A500,"=1",A1:A500,"<=10 ")
--ron
Obviously, if you want to include "1", and if the smallest value in your range
is "1", then the above formulas can be simplified; but I left them the way they
are so as to provide examples you could use for other ranges.
--ron
|