Average of numbers within a range meeting certain criteria

[opal23k]

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.

 

[Domenic]

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!

 

[David Billigmeier]

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,

 

[opal23k]

Thank you David!

 

[opal23k]

Thank you Domenic!

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!

 

[Dodo]

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.

I do not know where you have room in the sheet; suppose columns to the
right of N are free.

When N8 contains the header: Score

Put this text in P1 - Q1 - R1 - S1: Score

Put this in P2: <80
Put this in Q2: >=80
Put this in R2: <121
Put this in S2: >=121

In P10 the formula for Low: =DAVERAGE(N8:N154,"score",P12)
In P12 the formula for Med: =DAVERAGE(N8:N154,"score",Q1:R2)
In P14 the formula for Hi: =DAVERAGE(N8:N154,"score",S1:S2)

This way you see the criteria for the average groups and can easily change
them.

By making a criteria range across 2 columns and the criteria in the same
row, you create an AND operator.
Would you put the criteria in the same column in 2 rows, you'll get an OR
operator.