Average of numbers within a range meeting certain criteria
 

I have a column of test scores - N9:N154.

Scores can be grouped into low (<=79), average (80<=x=120), and high
(=121).

I want to have one formula for each group that will find the average score
for that group.

The logic is something like:
Low group: find the average of all the scores that are less than or equal to
79 within N9:N154.
Middle group: find the average of all the scores between 80 and 120 within
N9:N154.
High group: find the average of all the scores higher than or equal to 121
within N9:N154.

Try the following formula that need to be confirmed with
CONTROL+SHIFT+ENTER, not just ENTER...

=AVERAGE(IF(N9:N154<=79,N9:N154))

=AVERAGE(IF((N9:N154=80)*(N9:N154<=120),N9:N154))

=AVERAGE(IF(N9:N154=121,N9:N154))

Hope this helps!

In article ,
"opal23k" wrote:

I have a column of test scores - N9:N154.

Scores can be grouped into low (<=79), average (80<=x=120), and high
(=121).

I want to have one formula for each group that will find the average score
for that group.

The logic is something like:
Low group: find the average of all the scores that are less than or equal to
79 within N9:N154.
Middle group: find the average of all the scores between 80 and 120 within
N9:N154.
High group: find the average of all the scores higher than or equal to 121
within N9:N154.

Opal - these formulas will give you what you're after, confirm with
ctrl+shift+enter

Low Average:
=AVERAGE(IF(N9:N154<=79,N9:N154,""))

Middle Average:
=AVERAGE(IF(AND(N9:N154=80,N9:N154<=120),N9:N154, ""))

High Average:
=AVERAGE(IF(N9:N154=121,N9:N154,""))

Hope that helps,

Thank you David!

"David Billigmeier" wrote:

Opal - these formulas will give you what you're after, confirm with
ctrl+shift+enter

Low Average:
=AVERAGE(IF(N9:N154<=79,N9:N154,""))

Middle Average:
=AVERAGE(IF(AND(N9:N154=80,N9:N154<=120),N9:N154, ""))

High Average:
=AVERAGE(IF(N9:N154=121,N9:N154,""))

Hope that helps,

Thank you Domenic!

"Domenic" wrote:

Try the following formula that need to be confirmed with
CONTROL+SHIFT+ENTER, not just ENTER...

=AVERAGE(IF(N9:N154<=79,N9:N154))

=AVERAGE(IF((N9:N154=80)*(N9:N154<=120),N9:N154))

=AVERAGE(IF(N9:N154=121,N9:N154))

Hope this helps!

In article ,
"opal23k" wrote:

I have a column of test scores - N9:N154.

Scores can be grouped into low (<=79), average (80<=x=120), and high
(=121).

I want to have one formula for each group that will find the average score
for that group.

The logic is something like:
Low group: find the average of all the scores that are less than or equal to
79 within N9:N154.
Middle group: find the average of all the scores between 80 and 120 within
N9:N154.
High group: find the average of all the scores higher than or equal to 121
within N9:N154.