a condition question

[Penny]

I have two columns of data. I want to know the average GPA of only those
students at Carter Middle School. Help?

GPA(COL A) School (COL H)
3.3 Carter Middle School
2.3 Carter Middle School
2.8 Centennial
4.0 Carter Middle School

[Bob Umlas]

=SUMPRODUCT((B1:B4="Carter Middle
School")*A1:A4)/SUMPRODUCT(--(B1:B4="Carter Middle School"))

"Penny" <[email protected]> wrote in message
news:[email protected]...
>I have two columns of data. I want to know the average GPA of only those
> students at Carter Middle School. Help?
>
> GPA(COL A) School (COL H)
> 3.3 Carter Middle School
> 2.3 Carter Middle School
> 2.8 Centennial
> 4.0 Carter Middle School
>

[Penny]

I should have been more specific. I thought you were going to use the
average function and then I was hoping to figure out the rest. I also need
to be able to compute the standard deviations, minimum, and maximum.

Thanks, Penny


"Bob Umlas" wrote:

> =SUMPRODUCT((B1:B4="Carter Middle
> School")*A1:A4)/SUMPRODUCT(--(B1:B4="Carter Middle School"))
>
> "Penny" <[email protected]> wrote in message
> news:[email protected]...
> >I have two columns of data. I want to know the average GPA of only those
> > students at Carter Middle School. Help?
> >
> > GPA(COL A) School (COL H)
> > 3.3 Carter Middle School
> > 2.3 Carter Middle School
> > 2.8 Centennial
> > 4.0 Carter Middle School
> >
>
>
>

[Max]

I've responded further to you in your earlier thread, suggesting the use of
AVERAGE(IF(...)) array formulas. Take a look there.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Penny" <[email protected]> wrote in message
news:[email protected]...
> I should have been more specific. I thought you were going to use the
> average function and then I was hoping to figure out the rest. I also
need
> to be able to compute the standard deviations, minimum, and maximum.
>
> Thanks, Penny

[B. R.Ramachandran]

If you don't mind creating an extra column, you could do one of the following:

Lee's assume that the GPA's are in Column A and the names of schools in Col
H, and that Col I is empty; use the formula =IF(H1="Carter Middle
School",A1*1,"") in the cell I1 and drag the formula down the column. Column
I thus created will contain GPA values only for Carter Middle Schhol in the
respective rows and will be blank in rows corresponding to other schools.
Now you can find the average, stdev, max, and min for Col I.

An alternative approach may be,

Create a dummy column (say Column I) with consecurive numbers,
1,2,.........n, and sort the spread sheet in ascending/descending order of
Col H (containing school names). Data for the Carter Middle School would be
in a group, and you can calculate the average, etc., for that range. Cut and
paste those values with paste special, so that they would not change if you
were to restore the spread sheet to the original order (by resorting the
spread-sheet with the dummy column, i.e., Column I, you created).

B.R. Ramachandran

"Penny" wrote:

> I have two columns of data. I want to know the average GPA of only those
> students at Carter Middle School. Help?
>
> GPA(COL A) School (COL H)
> 3.3 Carter Middle School
> 2.3 Carter Middle School
> 2.8 Centennial
> 4.0 Carter Middle School
>