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 <[email protected]>,
"opal23k" <[email protected]> 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 <[email protected]>,
> "opal23k" <[email protected]> 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.
>

"=?Utf-8?B?b3BhbDIzaw==?=" <[email protected]> wrote in
news:[email protected]:

> 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",P1:P2)
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.


--

It is I, DeauDeau
(Free after monsieur Leclerc in 'Allo, 'allo)