Average of Certain Range of Scores between 70-100

I have a question? I am tracking student progress. I want to find the average of students that scored 70 or above. I get all of their scores in excel, but I do not know how to do this fast?


Student A: 80
Student B: 90
Student C: 50
Student D:30
Student E:40
Student F:20
Student G:10
Student H: 100
Student I: 70

I know I can sort the students "smallest to highest" and then manually select the students that scored 70 or above to get the average, but that is too time consuming. Please help!


Sincerely,

Damon Jasso
Ortiz Middle School

If your scores are in column B

=AVERAGEIF(B1:B9,">=70")

- Moo

Originally Posted by Moo the Dog

If your scores are in column B

=AVERAGEIF(B1:B9,">=70")

- Moo

I MADE A MISTAKE! I mean I need an average of the students that scored 70% or above out of all of the students.

For example: If I had 10 kids, and 4 made a 70% or above, then the pass rate is: 40%

I apologize!

DAMON JASSO
Ortizmiddleschool.org

OK, if your scores are in column B, try this:

Formula:

=COUNTIF(B1:B9,">=70")/COUNT(B1:B9)

-Moo

This is awesome! One last question, what if I apply another data set next the the student name and I want to filter it to get a more detailed average?

For Example

1 AA Student A 90
2 AA Student B 60
3 LEP Student C 40
4 AA Student D 70
5 AA Student E 80
6 LEP Student F 40

I would put the filter on for "AA" and then I would want the percentage of students that scored 70% out of the number of students that took the test? Thank you! If I have too, I suppose could paste values but that is time consuming...

Sincerely,

Damon Jasso
www.ortizmiddleschool.org

To change the lookup from AA to LEP would require you to change the formula (replacing "AA" with "LEP" twice. The better way would be to store the lookup value (AA, LEP, etc.) in a cell.

For instance, You could put the lookup value in cell D1 (AA), and the score that constitutes a passing grade in D2 (70), then the formula would be:

Formula:

=COUNTIFS(A1:A9,$D$1,C1:C9,">="&$D$2)/COUNTIF(A1:A9,$D$1)

Just some food for thought.

Of course, the other route to go is a Pivot Table, suggested above by oeldere.

- Moo

Your my hero... Is there a donate button on here somewhere?

DAMON

You're very welcome. Glad to help.

There isn't a donate button - we do this for (fun?) free, but you can click on the little star below any contributor's name (to the left, at the bottom of their post) who has helped find a solution for your issue.

- Moo