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]

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

 

[Max]

I've responded further to you in your earlier thread, suggesting the use of
AVERAGE(IF(...)) array formulas. Take a look there.

 

[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