BaPsy University Admission Calculator

Project Deep Dive

The BaPsy admission calculator started as a single Excel workbook built for one client, but its real complexity wasn't a single formula - it modeled how each individual German university scores an applicant's Abitur results against that university's own published admission criteria, a scope that grew further when the client came back for a refresh.

What the spreadsheet did

The delivered workbook was a six-sheet model: an Input sheet captured the student's Abitur average, their achieved and maximum point totals, their home federal state, and a set of subject and qualification checkboxes; an info sheet held the per-university constants each formula referenced; a Calculation sheet ran one dedicated formula block per participating university; a Conversion tables sheet held dozens of two-column lookup tables translating a computed score into an admission-quota percentile via VLOOKUP; and an Output sheet pulled the final percentile from each block into one results view, fed by option lists on a separate input-list sheet. Every university's block followed the same underlying shape - normalize the entered Abitur figures into a comparable score, subtract that university's published cutoff, then look the difference up in its own conversion table, wrapped in an IFERROR fallback for scores outside the table's range. A subset of universities layered in extra prerequisite adjustments - subject grades, a completed vocational qualification, volunteer service - as additional terms before that same lookup step.

How we converted it

We rebuilt the workbook as a WordPress-plugin calculator: one shared form collects the applicant's Abitur figures, state, and qualification answers once, then submits them by AJAX to a single PHP endpoint. Each university's Calculation-sheet block became its own PHP function, with every VLOOKUP re-expressed as the same two-column table walked in a loop, and each fixed cutoff constant and IFERROR fallback message ported over exactly, so an out-of-range score fails the same way the spreadsheet always did instead of guessing at a number. We verified every ported function by running matching sample inputs through both the workbook and the plugin side by side until the percentiles lined up. When the client returned with a refreshed 2025 workbook covering a substantially longer list of universities, we ported the newly added blocks the same way and updated the plugin's cutoff figures to the new admissions cycle - the kind of ongoing maintenance the original spreadsheet was never built to support on its own.

What changed for the client

For students, this replaced a workbook whose nested logic and conditional formatting only made sense to someone comfortable navigating raw Excel with a guided form that shows just the fields relevant to their answers and returns a plain pass/fail-style read on their chances at each university. That guided flow is what the client's own account of the project credits with cutting the confused, field-by-field support emails the spreadsheet used to generate. For the client, maintaining admission criteria also stopped meaning hand-editing nested formulas - updates now go through structured data behind the same calculation logic, which is exactly what let the 2025 refresh add coverage for far more universities without rebuilding the tool from scratch.