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
Hi James,
Add a Semester column in Student Results table with this formula:
=IF(OR([@Quarter]="Q1",[@Quarter]="Q2"), 1,2)
Then, instead of referring to quarters in COUNTIFS(dt.StudentResults[Quarter],B9....), use Countifs(dt.StudentResults[Semester],1,.....) to refer to semester 1 (or 2).
For Full year, removing Quarter or Semester criteria from formulas should work.
Thank you Catalin 
