Methodology

Methodology: CCNY Textbook Cost Audit Analysis

In this document I will describe the process used to produce the sections counts, and various figures highlighted in our report. This is for the years 2023 and 2025.

Data Source

Raw section-level textbook adoption data for City College of New York was obtained from CUNY's OAREDA (Office of Academic Reporting, Evaluation, and Data Analysis) textbook reporting files. The terms covered were Fall 2023 (Term # 1239) and Fall 2025 (Term # 1259). Each file contains one row per assigned textbook or material. What this means is that a single class section can appear as multiple rows if more than one material is assigned to it.

Imported into Google Sheets

The raw file from OAREDA was then imported into Google Sheets as a new sheet (File → Import → Insert new sheet(s)), rather than copied and pasted cell-by-cell. The imported tab was kept as-is and used as the single source for all subsequent pivot tables. Before building any pivot tables, a handful of rows were checked against the original source to confirm Term, Class #, Instructor, and Subject values had imported correctly and stayed aligned row-for-row. Once that was confirmed I began the use of PIVOT tables, a unique function of Google Sheets, to create tables of the data through setting choices.

Deduplication Approach

Class # is unique per section within a term but repeats across rows when a section has multiple assigned materials. As a result, all section count figures treat Class # (filtered by unique) as the counting unit. What this means is a section with three assigned materials still counts only once, instead of three times. This was implemented in each pivot table by aggregating the Class # field with Google Sheets' COUNTUNIQUE function, rather than a plain row count (COUNTA). This is all chosen from the pivot tables' editing settings tab.

Pivot Table Configurations

All pivot tables were built from the same source range (the full Raw Data tab). Each Google Sheets tab is named and configured as shown below:

Derived Figures and Known Limitations

Percentages (e.g., ZTC share of completed sections) were calculated directly from the pivot table data output, for example:

ZTC % of completed sections = (ZTC count, Complete) / (Total Complete sections)

Two things to mention, since they came up when checking the data myself:

• In any table with a Grand Total column that sums unique Class # values across both terms (2023 + 2025), that combined total will be slightly too low. A small number of Class # values happen to repeat between Fall 2023 and Fall 2025 by coincidence, so COUNTUNIQUE undercounts when both terms are combined into a single total because it cuts the number down. This does not affect any single-term column, only a combined Grand Total column. You can simply add 2023 to 2025 manually.
• Second, a small number of sections (19 in 2023, 14 in 2025) are flagged as both ZTC and LTC. A "traditional adoption" figure calculated as Complete − ZTC − LTC will double-subtract these sections. So, what I recommend is instead calculating everything as “Complete − (ZTC + LTC − Both).”
  • For example, we got: 2023; 69.8% traditional textbook/adoption (1,829 / 2,618)
  • And we got: 2025; 66.5% traditional textbook/adoption (1,612 / 2,423)