Average Calculation based on criteria

**Grandma8** · 07-14-2010, 07:48 AM

I am using the formula below for averaging a student’s preparedness and it seems to work fine.
=IF(ISERR(COUNTIF(Progress!$D2:$O2,"y")/COUNTA(Progress!$D2:$O2)),"",COUNTIF(Progress!$D2:$O2,"y")/COUNTA(Progress!$D2:$O2))

Now I’d like to create a similar formula to determine average preparedness of an entire class, by month. Cells D2:D123 contain Y or N for preparedness for one month and cells A2:A123 identifies the class number. These cells are on the sheet titled Progress. I’ve spent days trying to write the formula but I finally gave up because I could not figure out how to add the class criteria. Below is an overview of the workbook.

Basically I'd like to display the monthly average of each class's preparedness. However, if a cell is blank (doesn't contain a Y or N), I do not want to count the cell as some students are not in attendance for certain months.

Workbook sheets:
Progess: Worksheet to enter data for student preparedness, by month.
Column A: Class Number
Column B: First Name
Column C: Last Name
Columns D-O: Months, beginning with July

Percentages: Worksheet to summarize average class preparedness by month. (I will populate new formula in cells beneath each month.)
Column A: Class Number (linked from Students worksheet)
Column B: Class Percentage To Date (linked from Students worksheet)
Column C: Headcount (Linked from Students Worksheet)
Columns D-O: Months of the school year, beginning with July

Students: Worksheet provides general date regarding student progress and classroom needs.
Column A: Class Number (used for calculations - numbers only)
Column B: Student's average preparedness based on data in worksheet Progress.
Column C: Class Number based on text (some classes contain test and numbers)
Column D: First Name
Column E: Last Name
Column F: Activity Date (Text field)
Columns G-I: Text fields for miscellaneous information

**Chemistification** · 07-14-2010, 08:05 AM

Can you post a sample spreadsheet and i'll have a go...

Edit: Tried creating one from your instructions but want to know im working on the right thing. Just take out or amend any sensitive information

**Grandma8** · 07-14-2010, 08:31 AM

Hopefully the upload worked. My first time uploading a file.

Any help would be appreciated.

**Chemistification** · 07-14-2010, 10:29 AM

See how this works for you. Probably not the prettiest way of doing it and im sure there will be better ways but think it works. Give it a check and if you need it explaining let me know. Some of the formulas are arrays so if your getting errors expanding it to the other columns watch out for this.

**Grandma8** · 07-14-2010, 12:41 PM

I am pretty sure I understand your logic and will complete the worksheet with all the monthly yes and no columns, but will move them to the end of the Progress worksheet so that the person using the form doesn't have to remember to hide columns. It will take me a day or so to complete but will get back to you about the status. Thank you for your help.