Formula to calculate student results over multiple quarters during the academic year, to then identify what % of students achieved > x%|General Excel Questions & Answers|Excel Forum|My Online Training Hub

Hi, i'm using MS Excel 365.

Earlier this year Mynda kindly shared with me how to calculate what % of students scored >x% in the quartley exam results.  We compare the results against two measures - Success Rate (SR) and Proficiency Rates (PR). It was achieved using a formula which referenced the measures (>x%) on the dashboard.

=SUM(COUNTIFS

(dt.StudentResults[Academic_Year],A9,dt.StudentResults[Quarter],B9,dt.StudentResults[Grade],C9,dt.StudentResults[Subject],D9,dt.StudentResults[Attribute],H9,dt.StudentResults[Value],'SR-PR Dashboard'!$O$2)

/

COUNTIFS(dt.StudentResults[Academic_Year],A9,dt.StudentResults[Quarter],B9,dt.StudentResults[Grade],C9,dt.StudentResults[Subject],D9,dt.StudentResults[Attribute],H9))

The school would now like to see same results but over multiple quarters:

• The results for Semester 1 - By student, sum of Q1 and Q2 results divide by 2, then calculate % that are > x%
• The results for Semester 2 - By student, sum of Q3 and Q4 results divide by 2, then calculate % that are > x%
• The results for full academic year - By student the sum of Q1, Q2, Q3, Q4 divide by 4, then calculate % that are > x%

Attached is my workbook, containing the worksheets

• SR-PR Dashboard  - this has the x% value
• SR-PR Workings - contains the formula in cells E9 to E14 and F9 to F14.
• dt.StudentResults - data set

Any help would be greatly appreciated.

James