Need average of numbers that occur within range

[Techhead]

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

 

[Rick Rothstein \(MVP - VB\)]

Give this a try...

=SUMIF(A1:A500,"<=10")/COUNTIF(A1:A500,"<=10")

Rick

 

[Mike H]

Hi,

try this and enter as an array with Ctrl+shift+enter

=AVERAGE(IF((A1:A500>=1)*(A1:A500<=10),A1:A500,FALSE))

Mike

 

[Ron Rosenfeld]

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

 

[Ron Rosenfeld]

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

 

[Techhead]

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")-COUNTI­F(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

Thank you, this worked out well. I used this syntax. I replaced the
range with the entire row series. Thanks for your help!

=AVERAGEIFS(G:G,G:G,">=1",G:G,"<=10")

 

[Ron Rosenfeld]

Thank you, this worked out well. I used this syntax. I replaced the
range with the entire row series. Thanks for your help!

=AVERAGEIFS(G:G,G:G,">=1",G:G,"<=10")

Glad to help. Thanks for the feedback.
--ron