Operationalising CTT and IRT in Spreadsheets: A Methodological Demonstration for Classroom Assessment

Abstract

The evaluation of student performance often relies on basic spreadsheet outputs that provide limited insight into item functioning. This study presents a methodological demonstration showing how widely available spreadsheet software can be transformed into a practical environment for psychometric analysis. Using a simulated dataset of 40 students responding to 20 dichotomous items, spreadsheet formulas were developed to compute descriptive statistics and Classical Test Theory (CTT) indices, including item difficulty, discrimination, and corrected item–total correlations. The demonstration was extended to Item Response Theory (IRT) through the implementation of 1PL, 2PL, and 3PL logistic models using forward-calculated item parameters. A smaller dataset of 10 students and 10 items was used to illustrate the interpretability of the indices and the generation of Item Characteristic Curves (ICCs). Results show that spreadsheets can support teachers in in-terpreting test data beyond total scores, enabling the identification of weak items, refinement of distractors, and construction of small-scale item banks aligned with competence-based curricula. The approach contributes to Sustainable Development Goal 4 (SDG 4) by promoting accessible, equitable, and high-quality assessment practices. Limitations include the instability of IRT parameter estimation in small samples and the need for teacher training. Future research should apply the approach to real classroom data, explore automation within spreadsheet environments, and examine the integration of artificial intelligence for adaptive assessment.

1. Introduction

The fair, reliable, and credible assessment of student learning remains a critical and sensitive issue across educational systems. Although learning primarily occurs during the instructional process [1,2], assessment practices traditionally focus on learning outcomes, whether through formative, ongoing evaluation or summative final testing. Throughout the learning process, corrective and cognitive feedback play a crucial role [3], as does self-assessment, which enables learners to monitor and evaluate their own understanding [4,5]. In formative assessment, teachers employ a wide range of techniques, including portfolios, case studies, group work, quizzes, and multiple-choice tests. Summative assessment typically relies on written examinations and closed-response formats.

This article focuses exclusively on closed-response assessment instruments, including multiple-choice items and other fixed-response formats (e.g., true/false, matching, and short-answer with predefined responses). For such instruments to be considered valid and credible, careful item design is essential, and items must undergo scientific content validation [6]. Content validation seeks to ensure that test items adequately represent the domain of knowledge being assessed and is commonly achieved through expert review supported by quantitative indicators, such as the Content Validity Index (CVI) or Content Validity Ratio (CVR). This process typically involves defining the content domain, selecting subject-matter experts, calculating validity indices, and refining items based on relevance and representativeness.

Despite the importance of this rigour, closed-response tests are not always constructed following systematic validation procedures, even though they are widely used throughout compulsory education for both formative and summative purposes. In many contexts, teachers continue to rely on paper-based scoring grids, often consisting of manually completed tables. Others use digital scoring templates provided by educational publishers alongside their assessment instruments.

The widespread availability of computers and digital technologies has facilitated the use of software tools capable of replacing handwritten assessment records. Numerous free and commercial spreadsheet applications are now available, and many educational publishers offer digital scoring grids integrated with their tests. However, the analytical potential of these tools is frequently underused. In most cases, they provide only basic outputs, such as: (i) individual student total scores; (ii) score conversion to qualitative grading scales; (iii) class averages; and (iv) graphical distributions of class results.

Integrating more robust forms of test analysis into everyday assessment practices can improve the alignment of teaching strategies with students’ learning needs and, consequently, enhance student performance. To achieve this, teachers require not only basic digital competencies but also foundational knowledge of statistical analysis that can be readily applied within commonly used tools such as spreadsheets. When appropriately designed, these tools can support more detailed analyses of student performance at both individual and class levels, as well as provide valuable information about the quality of the assessment instrument itself (e.g., item difficulty, discrimination, and reliability). Classical Test Theory and Item Response Theory are theories that can support teachers in making student assessment more tailored to their performance and making the assessment more reliable [7,8,9]. In addition to its methodological contribution, this study aligns with Sustainable Development Goal 4 (SDG 4), which emphasises inclusive and equitable quality education [10]. Although the analysis of dichotomous multiple-choice items represents only one component of the broader assessment landscape, strengthening teachers’ capacity to interpret item functioning contributes to more transparent, fair, and pedagogically informed decision-making. By enabling teachers to diagnose item quality and detect sources of difficulty or discrimination using widely available spreadsheet tools, the proposed approach supports more equitable assessment practices within classroom contexts. In this sense, the operationalisation of psychometric analysis in accessible digital environments reinforces the principles of SDG 4 by promoting fairer evaluation processes, enhancing data-informed pedagogical decisions, and expanding teachers’ ability to implement high-quality assessment practices without the need for specialised software.

2. Theoretical Framework

2.1. Classical Test Theory and Item Response Theory

Classical Test Theory (CTT) has long been used in the analysis of objective tests, particularly in post-exam evaluations at higher education levels, due to its simplicity and accessibility [11]. Its core concepts form the foundation of modern assessment and rely on elementary algebraic and statistical operations that can be implemented in common spreadsheet software [12,13,14]. The classical model, $X=T+E$, conceptualises the observed score (X) as the sum of the true score (T) and measurement error (E) [14,15,16,17]. CTT’s weak assumptions make it suitable for practical educational contexts, and it provides reliable estimates even with relatively small samples [11,12,13,18]. It also tolerates suboptimal item formulation, as deficiencies can be compensated by increasing the number of items [12,19]. Despite these advantages, CTT presents significant limitations that motivated the development of Item Response Theory (IRT) [13,20]. Item parameters such as difficulty and discrimination are sample-dependent [12,19,20,21,22,23], meaning that a high-ability group may artificially inflate difficult items [19,24,25]. CTT is also test-dependent: easier tests yield higher scores even when actual performance is unchanged [13,19,23]. The assumption of uniform measurement error is unrealistic, as error varies across performance levels [13,19]. Furthermore, CTT focuses on the total score [19,21,23], offering limited insight into item-level functioning [11]. It assumes equal item contribution to the total score [11,12], which may distort results in heterogeneous populations [18], and it struggles in contexts where all examinees answer items identically, sometimes producing technical errors such as divisions by zero [18].

IRT was developed to address these limitations by modelling the probability of a correct response as a function of latent ability [11,13,17,19,20]. A key advantage is parameter invariance: item parameters are independent of the sample, and ability estimates are independent of the specific items administered [12,13,14,19]. IRT provides detailed item-level analysis, enabling the detection of low or negative discrimination and supporting more accurate estimation of competence [11,13,19,23]. The Rasch Model places item difficulty and person ability on the same logistic scale, clarifying their relationship [18]. IRT also offers more sophisticated approaches to modelling measurement error [14], detecting bias [18], and accounting for guessing through the three-parameter logistic model (3PL) [23]. It further supports the construction of calibrated item banks, enabling consistent difficulty across test administration [21]. However, IRT requires strong assumptions, such as unidimensionality and local independence, and is more complex to implement [11,12,13,15,24,25].

In summary, IRT provides a more rigorous and precise measurement than CTT [12,14,18], but CTT remains widely used due to its simplicity and minimal requirements [11,12,13,19]. Many authors recommend combining both approaches: CTT for basic statistical analysis and IRT for item-level modelling and ability estimation [14,19].

2.2. Logistic Models in Item Response Theory (1PL, 2PL, 3PL)

IRT logistic models describe the probability that an examinee with latent ability θ answers an item correctly [9,26,27]. Parameter invariance is central to these models: item parameters do not depend on the examinee sample, and ability estimates do not depend on the specific items administered [9,28,29]. The One-Parameter Logistic Model (1PL), or Rasch model, assumes that item difficulty (b) is the only parameter influencing the probability of success, with discrimination fixed at a = 1 and no guessing (c = 0) [26,27,29]. The Two-Parameter Logistic Model (2PL) adds the discrimination parameter (a), which determines how sharply the item differentiates between examinees of different abilities [26,27,28]. The Three-Parameter Logistic Model (3PL) incorporates difficulty (b), discrimination (a), and a guessing parameter (c), making it particularly suitable for multiple-choice items where low-ability examinees may answer correctly by chance [26,27,28,30]. The c-parameter represents the lower asymptote of the item characteristic curve. However, parameter recovery becomes less precise as model complexity increases, often resulting in higher Root Mean Square Error (RMSE) values [9,27].

In summary, the 1PL, 2PL, and 3PL models differ in the number of parameters considered, and model selection should reflect the characteristics of the data and the intended use of the assessment [26].

2.3. Competence-Based Assessment Using CTT and IRT

Competence-Based Education (CBE) has become a central paradigm in international educational policy, emphasising the integrated mobilisation of knowledge, skills, attitudes, and values [26,31,32]. Assessment plays a strategic role in CBE, as students must demonstrate mastery of clearly defined learning goals [33,34,35]. Assessing transversal competences—such as critical thinking, creativity, or digital literacy—poses particular challenges, often requiring more sophisticated measurement approaches [36,37]. CTT and IRT contribute to competence-based assessment by supporting the development of reliable and valid instruments [38]. Item-level analysis enables the refinement of items, the identification of weaknesses, and the construction of high-quality item banks with known psychometric properties [38]. IRT, in particular, supports mastery-based progression by estimating competence levels independently of instructional time [35] and by providing diagnostic information that guides pedagogical decisions [36]. Competence-based formative assessment relies on timely, actionable feedback that helps students monitor their learning and supports teachers in adapting instruction [35,39]. Reliable data are essential for informing educational policy and evaluating the impact of curricular reforms [36,37]. Psychometrically sound assessments also support the use of AI-driven personalised learning pathways [40]. Assessment fulfils multiple functions within competence-based education, including diagnostic, formative, and summative purposes. Diagnostic assessment identifies students’ initial levels of mastery and informs instructional planning; formative assessment supports ongoing learning through timely feedback; and summative assessment certifies achievement at the end of an instructional period [32,37]. The quality of the items used in each of these functions directly affects the validity of the inferences drawn [15,23,26]. CTT and IRT contribute to these functions by providing statistical evidence that helps teachers select, refine, and validate items, ensuring that assessment decisions are grounded in reliable and interpretable data [12,13,17,18,20].

In summary, CTT and IRT provide complementary tools for ensuring that competence-based assessment is conducted with scientific rigour, enabling valid inferences about student mastery and supporting continuous improvement of assessment instruments [35,36,38].

2.4. Multiple-Choice Items and Psychometric Analysis

Multiple-choice questions (MCQs) are widely used due to their efficiency, objectivity, and capacity to cover broad curricular content [41,42,43,44,45,46,47]. Well-constructed MCQs can assess higher-order cognitive processes, including analysis, evaluation, and problem-solving, in line with Bloom’s taxonomy [42,43,48,49,50]. However, constructing high-quality MCQs is demanding and prone to error [41,49]. IRT is particularly relevant for MCQ analysis because it evaluates item difficulty and discrimination with greater precision than CTT [41]. The discrimination parameter is essential for determining an item’s ability to differentiate between competence levels [41,51]. Item analysis—both pre-validation and post-validation—is indispensable for building item banks [42,50,52,53]. Research also shows that three-option MCQs (one correct, two distractors) are often as reliable as four- or five-option items, as plausible distractors are difficult to construct [46,47,48,49,54]. Distractor quality is critical: functional distractors attract at least 5% of examinees, while non-functional distractors should be revised or removed [41,49,52,55]. Effective distractors must be plausible, homogeneous, grammatically consistent, and free of clues [50,51,55].

In summary, MCQs are versatile and reliable when constructed and analysed rigorously. CTT and IRT provide essential tools for evaluating item quality and ensuring alignment with cognitive and curricular goals.

2.5. The Literature Gap: Democratising Psychometric Analysis Through Accessible Tools

Despite the shift toward competence-based assessment and the recognised need for psychometric rigour, a significant gap remains in the practical application of CTT and IRT by classroom teachers. Most existing research focuses on large-scale assessments using specialised software, creating technical and financial barriers for everyday practitioners. Teachers typically rely on basic spreadsheet outputs—such as total scores and class averages—that provide limited insight into item functioning.

Recent studies highlight inconsistencies in item quality across years in state-level examinations [56] and point to emerging psychometric models, such as the four-parameter logistic model (4PLM), which account for guessing and carelessness [57]. However, these advanced models remain inaccessible to most teachers due to the absence of simplified implementation guides. There is also a lack of pedagogical literature translating psychometric formulas into spreadsheet-based functions, despite the potential of tools such as Excel and Google Sheets to support detailed item analysis [58].

3. Research Questions and Objectives

This study seeks to address three main research questions, together with the objectives to be achieved:

• RQ1—Which statistical analyses, feasible through the use of spreadsheets, can improve the robustness of test analysis?
• O1—To enhance spreadsheet-based scoring tools with accessible formulas that support improved statistical analysis of assessment instruments.
• RQ2—How can statistical results generated in spreadsheets be meaningfully interpreted by teachers?
• O2—To present the foundational principles necessary for effective statistical analysis of applied assessment instruments.
• RQ3—How can statistical analysis results be used to improve item construction or support the development of an assessment item database?
• O3—To use statistical evidence to refine test items, inform assessment strategies, and structure a reusable item database.

4. Methodology

The study adopts an applied methodological demonstration design, illustrating how Classical Test Theory (CTT) and Item Response Theory (IRT) analyses can be operationalised using accessible spreadsheet tools [8,50]. The intention is not to conduct an empirical validation based on real student data, but rather to show the feasibility and pedagogical relevance of spreadsheet-based psychometric analysis. To this end, all analytical procedures are demonstrated using a simulated dataset constructed to reflect typical response patterns found in closed-response assessment instruments. This approach allows a transparent exposition of the computational steps involved while avoiding the ethical and contextual constraints associated with real assessment data. Real classroom data were not included due to ethical and administrative constraints, particularly the requirement for institutional authorisation to collect, store, and process student responses, even in anonymised form. Although the use of anonymised data might appear feasible, institutional guidelines stipulate that formal approval is required for any handling of student assessment information. To ensure full compliance with these requirements, simulated data were used. This approach also provides complete control over item characteristics and response patterns, ensuring that the examples presented in the manuscript are pedagogically transparent, replicable, and suitable for teacher training and professional development contexts. Although the dataset is simulated, it reflects typical response patterns observed in lower- and upper-secondary education, particularly in subjects such as science and mathematics where closed-response formats are frequently used. The study addresses the identified gap by demonstrating how widely available spreadsheet software can be transformed into a robust analytical environment for psychometric analysis. By enabling teachers to compute corrected item–total correlations and apply Rasch or logistic models, spreadsheet-based procedures can support item refinement, distractor evaluation, and the development of calibrated item banks without requiring specialised software or programming skills. The methodological demonstration responds directly to the research questions by identifying the statistical indices that can be feasibly computed in spreadsheets (RQ1), clarifying how these indices may be interpreted by teachers (RQ2), and illustrating how such evidence can inform item refinement and the development of assessment item banks (RQ3). The overarching aim is to translate core psychometric principles into accessible analytical processes that can be implemented by educators with limited statistical training.

A simulated dataset was therefore created to represent plausible student responses to a closed-response assessment. The dataset comprises 40 hypothetical students responding to 20 dichotomously scored items (1 = correct; 0 = incorrect), each with four response options. The spreadsheet structure mirrors typical teacher-created scoring grids: students are represented in rows, items in columns, and additional columns compute total scores, corrected totals, and the upper and lower groups required for discrimination analysis. For each item, the correct answer is stored in a dedicated cell (e.g., E7), allowing the spreadsheet to automatically compare each student’s response with the key and compute item-level statistics without manual recoding. The sample size of 40 students is adequate for illustrating CTT procedures, which are known to produce stable estimates even with relatively small groups. In contrast, IRT models generally require larger samples for stable parameter estimation; however, because this study does not estimate parameters but instead demonstrates forward calculation of item response probabilities, the sample size is appropriate for the methodological purpose of the analysis. CTT procedures implemented in the spreadsheet include item difficulty, Rasch-based difficulty, item discrimination, corrected item–total correlations, and the probability of random guessing, following established approaches [20,25]. The IRT component extends the demonstration by computing response probabilities under the 1PL, 2PL, and 3PL logistic models using assigned item parameters. The two datasets served distinct analytical purposes. The larger 40 × 20 matrix was used for the full CTT and IRT demonstrations, including item difficulty, discrimination, corrected item–total correlations, and Rasch-based difficulty. The smaller 10 × 10 dataset was used exclusively for illustrative purposes, allowing readers to follow the computational steps more easily and to interpret the resulting indices in a compact and visually accessible format. This distinction ensures both methodological rigour and pedagogical clarity. All mathematical expressions and spreadsheet formulas used to operationalise these procedures are provided in Appendix A and Appendix B.

5. Results

The results illustrate how spreadsheet-based CTT and IRT analyses can be applied to a simulated dataset of 10 students responding to 10 dichotomous items. The purpose is to demonstrate interpretability, replicability, and pedagogical relevance rather than to generalise findings. The spreadsheet was organised to mirror typical teacher-created scoring grids: students were listed in rows, items in columns, and additional columns computed total scores, corrected totals, and the upper and lower groups required for discrimination analysis. The structure of the spreadsheet and all formulas used are fully documented in Appendix A and Appendix B, ensuring transparency and replicability.

5.1. Descriptive Statistics of Total Scores

Total simulated scores ranged from 3 to 9 points, with a mean of 6.2, a median of 6, and a standard deviation of 1.93. The distribution was approximately symmetric, indicating that the dataset was suitable for illustrating item-level analysis and the behaviour of the statistical indices.

5.2. Classical Test Theory Results

5.2.1. Item Difficulty

Item difficulty values (p-values) ranged from 0.20 to 0.90. Seven items fell within the recommended range of 0.30 to 0.80, indicating balanced difficulty. Item 4 was considered very easy (p = 0.90), and Item 8 was very difficult (p = 0.20). Because their p-values fall outside the recommended range, Items 4 and 8 would require revision in a real assessment context.

5.2.2. Rasch-Based Difficulty

Rasch difficulty parameters (b-values) ranged from −2.20 (very easy) to +1.39 (very difficult). Items with negative b-values (e.g., Items 1, 4, 9) were easier, and items with positive b-values (e.g., Items 5 and 8) were more challenging. The Rasch transformation aligned closely with the CTT difficulty results, confirming consistency between the two approaches.

5.2.3. Item Discrimination

Using the 27% method (top 3 vs. bottom 3 students), discrimination values ranged from 0.05 to 0.52. Items 1, 5, and 9 showed strong discrimination, and Items 4 and 8 showed weak discrimination. This indicates that the stronger items effectively differentiated between higher- and lower-performing students, whereas the weaker items did not.

5.2.4. Corrected Item–Total Correlations

Corrected item–total correlations ranged from 0.10 to 0.48. Items 1, 5, and 9 showed good alignment with the overall construct (r_it > 0.30), and Items 4 and 8 showed weak alignment (r_it < 0.20). The convergence between discrimination and corrected correlations reinforces the identification of strong and weak items.

5.2.5. Summary of CTT Findings

A compact summary of the CTT indices is presented in Appendix A,

Table A2. CTT indices, referenced in Section 5.2.1, Section 5.2.2, Section 5.2.3, Section 5.2.4 and Section 5.2.5 (difficulty p, Rasch difficulty b and corrected item–total correlation r_it).

Item	p-Value	b-Value	Discrimination	rit
I1	0.8	−1.39	0.45	0.42
I2	0.6	−0.41	0.28	0.29
I3	0.6	−0.41	0.31	0.27
I4	0.9	−2.2	0.1	0.15
I5	0.4	0.41	0.52	0.48
I6	0.7	−0.85	0.33	0.31
I7	0.6	−0.41	0.29	0.26
I8	0.2	1.39	0.05	0.1
I9	0.8	−1.39	0.44	0.39
I10	0.5	0	0.22	0.24

. Items 1, 5, and 9 consistently demonstrated desirable psychometric properties, while Items 4 and 8 underperformed across all indices and would require revision or replacement in a real assessment context.

5.3. Item Response Theory Results

5.3.1. Parameter Behaviour (Demonstration)

Using assigned parameters for demonstration, the 1PL, 2PL, and 3PL models produced probability curves consistent with expected psychometric behaviour. Items 1, 5, and 9 had strong discrimination values (a > 1.20), and Items 4 and 8 had weak discrimination (a < 0.70). Difficulty parameters (b) were consistent with the CTT and Rasch results. Guessing parameters (c) were appropriate for four-option multiple-choice items.

5.3.2. Item Characteristic Curves Interpretation

Item Characteristic Curves (ICCs) were generated in Excel using the θ-P(θ) values computed for three representative items (Items 1, 5, and 8). The θ scale ranged from −4 to +4 in increments of 0.5, and probabilities were calculated using the 3PL model with item-specific parameters. The resulting curves are shown in Figure 1, and the underlying data are presented in

Table 1. θ-P(θ) Values Used to Generate Item Characteristic Curves.

θ	P Item 1	P Item 5	P Item 8
−4	0.20	0.20	0.22
−3.5	0.20	0.20	0.22
−3	0.20	0.20	0.22
−2.5	0.21	0.20	0.22
−2	0.22	0.21	0.22
−1.5	0.25	0.23	0.23
−1	0.30	0.27	0.23
−0.5	0.38	0.34	0.24
0	0.48	0.44	0.25
0.5	0.59	0.56	0.26
1	0.69	0.68	0.28
1.5	0.78	0.78	0.30
2	0.85	0.86	0.33
2.5	0.90	0.91	0.36
3	0.93	0.94	0.39
3.5	0.95	0.96	0.42
4	0.96	0.97	0.45

. The ICCs display the expected psychometric behaviour for items with differing difficulty and discrimination levels. Item 1 shows a steep curve with an early rise, indicating an easy item with strong discrimination. Item 5 presents a similar steep slope but shifts slightly to the right, reflecting moderate difficulty and high discrimination. In contrast, Item 8 exhibits a shallow slope and a higher lower asymptote, suggesting weak discrimination and potential distractor issues. These patterns align with the CTT results, reinforcing the identification of strong and weak items.

5.3.3. Test Information Function (Conceptual)

The simulated test provided the highest measurement precision for ability levels between θ = −0.5 and +0.8, indicating that the test is most informative for students of average to slightly above-average proficiency. Items 5 and 9 contributed most to test information, while Items 4 and 8 contributed minimally.

5.4. CTT–IRT Comparison

The comparison between CTT and IRT results showed strong convergence: Items 1, 5, and 9 consistently emerged as strong performers across all indices, while Items 4 and 8 consistently underperformed. Rasch difficulty values aligned with CTT p-values, and 2PL discrimination parameters mirrored CTT discrimination. This coherence demonstrates that spreadsheet-based CTT and IRT analyses can jointly support evidence-based item refinement.

5.5. Spreadsheet Structure

The spreadsheet layouts used for the analyses are present in Appendix A and Appendix B. The CTT layout includes columns for item responses, total scores, difficulty, discrimination, and corrected totals. The IRT layout includes ability values and probability outputs for the 1PL, 2PL, and 3PL models, with item parameters stored in fixed cells. These descriptions allow teachers to replicate the analyses.

5.6. Pedagogical Interpretation

The combined CTT and IRT analyses demonstrate that spreadsheet-based psychometric tools can support teachers in identifying weak items, refining distractors and stems, building calibrated item banks, strengthening formative assessment practices, and aligning evaluation procedures with competence-based curricula. Within the simulated dataset, Items 4 and 8 clearly illustrate how statistical evidence can signal the need for item revision, whereas Items 1, 5, and 9 exemplify desirable psychometric behaviour and show how well-functioning items contribute to reliable and interpretable assessment results.

6. Discussion

This study aimed to demonstrate how spreadsheet software can be used to operationalise Classical Test Theory (CTT) and Item Response Theory (IRT) analyses in a manner accessible to teachers. The results obtained from the simulated dataset illustrate that spreadsheets can support a level of psychometric analysis typically associated with specialised software, thereby addressing the gap identified in the literature regarding the practical application of item analysis in everyday educational contexts [56,57,58,59]. All mathematical expressions and spreadsheet formulas referenced in this section are provided in Appendix A, Appendix B and Appendix C.

6.1. Interpretation of CTT Findings

The CTT indices generated in the spreadsheet—item difficulty, discrimination, and corrected item–total correlation—produced patterns consistent with established psychometric standards. Items with difficulty values within the recommended range and discrimination values above 0.30 were identified as strong performers, aligning with the literature that emphasises balanced difficulty and adequate discrimination for valid inferences [11,19,23]. The convergence between discrimination and corrected item–total correlations reinforces the reliability of the spreadsheet-based approach, as both indices consistently identified Items 1, 5, and 9 as high-quality items and Items 4 and 8 as problematic.

These findings support the argument that CTT remains a valuable framework for classroom assessment due to its conceptual simplicity and suitability for small samples [11,12,13,18]. By enabling teachers to compute these indices directly in spreadsheets, the approach enhances their capacity to evaluate item functioning without requiring advanced statistical tools.

6.2. Interpretation of IRT Findings

The IRT analyses provided complementary insights that extend beyond CTT. The discrimination parameter in the 2PL model mirrored the CTT discrimination index, while the difficulty parameter aligned with both CTT difficulty values and Rasch logits. The 3PL model further highlighted the influence of guessing, particularly for items with weak distractors, consistent with research emphasising the importance of accounting for lower asymptotes in multiple-choice items [26,27,28,29,30].

It is important to acknowledge, however, that IRT parameter estimation typically requires large samples to ensure stable and interpretable estimates [20]. In this study, parameters were assigned for demonstration purposes, avoiding the instability that would arise from estimating them with small samples. This limitation does not compromise the pedagogical value of the demonstration but should be considered when applying IRT in real educational contexts.

6.3. Pedagogical Implications for Competence-Based Assessment

The results demonstrate that spreadsheet-based psychometric analysis can meaningfully support competence-based assessment. Teachers can use these tools to:

• identify weak items and revise distractors, stems, or cognitive alignment;
• ensure that assessments measure intended competences with appropriate difficulty;
• build small-scale item banks with known psychometric properties;
• interpret student performance beyond total scores;
• provide targeted feedback based on item-level evidence.

These implications align with the literature emphasising the importance of rigorous assessment practices in competence-based education, where valid and reliable evidence is essential for diagnosing learning needs, supporting formative assessment, and guiding instructional decisions [33,34,35,36,37,38,39].

6.4. Feasibility, Practical Constraints and Ethical Considerations

While the spreadsheet-based approach is accessible and transparent, several practical constraints must be acknowledged: (i) although CTT indices can be computed reliably with small samples, IRT parameter estimation typically requires larger datasets, particularly for 2PL and 3PL models [20]; (ii) teachers require basic training in interpreting psychometric indices; without such training, there is a risk of misinterpretation or over-reliance on numerical outputs; (iii) spreadsheets are limited in their capacity to automate complex analyses or handle large datasets. Nevertheless, they remain a powerful entry point for teachers who lack access to specialised software.

Ethical issues arise when assessment data are used to inform pedagogical decisions. Teachers must ensure confidentiality, avoid over-interpretation of statistical indices, and maintain transparency regarding how assessment results are used. The spreadsheet-based approach supports ethical practice by making analytical procedures visible and replicable, reducing the opacity often associated with proprietary software.

6.5. Revisiting the Research Questions and Achievement of Objectives

The study’s research questions and objectives were fully addressed through the analyses conducted. The first research question (RQ1) examined whether spreadsheet software could operationalise CTT and IRT procedures in a teacher-friendly manner. The results confirmed that spreadsheets can compute key psychometric indices and implement 1PL, 2PL, and 3PL models, demonstrating that such tools offer an accessible alternative to specialised software. This directly fulfilled the objective of developing a transparent and replicable analytical framework.

The second research question (RQ2) explored the interpretability and pedagogical value of spreadsheet-generated indices. The findings showed consistent patterns across CTT and IRT, with strong items identified by multiple indicators and weaker items highlighted for revision. This confirmed that the spreadsheet-based approach yields meaningful insights aligned with established psychometric literature, thereby achieving the objective of illustrating how these analyses can support item refinement and competence-based assessment.

The third research question (RQ3) focused on the practical contribution of these analyses to educational practice. The study demonstrated that teachers can use item-level evidence to diagnose learning needs, improve test quality, and construct small-scale item banks. These outcomes fulfilled the objective of demonstrating the pedagogical relevance and practical feasibility of integrating psychometric analysis into everyday assessment.

Together, these results confirm that the study successfully met its aims: to provide an accessible methodological approach, to demonstrate the interpretability of spreadsheet-based psychometrics, and to show how such analyses can meaningfully support competence-based assessment.

6.6. Limitations and Future Research

A key methodological limitation concerns the estimation of parameters in the 2PL and 3PL models. These models generally require large samples to produce stable and interpretable discrimination and guessing parameters, and classroom-sized datasets may lead to substantial instability. Although this study avoided these issues by not estimating parameters, teachers applying IRT in real contexts should be aware that 2PL and 3PL estimation typically require specialised software and larger datasets to ensure psychometric validity.

The use of a simulated dataset also limits the ecological validity of the findings. Future research should apply the spreadsheet-based approach to real classroom data to examine its practical utility and robustness. Additional work could explore the automation of item analysis within spreadsheet environments, as well as the integration of artificial intelligence to support adaptive item selection and personalised learning pathways. Further investigation is also needed into the feasibility of using spreadsheets for larger datasets and more complex IRT models, including the 4PL model highlighted in the recent literature [57].

This study focuses specifically on dichotomous multiple-choice items and therefore does not address the assessment of complex performances typically associated with competency-based education. The methodology is most appropriate for formative purposes, supporting teachers in diagnosing item functioning and improving the quality of their test items. It does not replace assessment approaches that capture higher-order reasoning, argumentation, or the integration of knowledge, skills, and attitudes in authentic contexts. These limitations should be considered when interpreting the potential contribution of the proposed approach to broader educational goals.

7. Conclusions

This study demonstrates that spreadsheet software can serve as an accessible and transparent environment for conducting Classical Test Theory (CTT) and Item Response Theory (IRT) analyses in educational contexts. By operationalising key psychometric procedures within a familiar tool, the study addressed the need for practical, teacher-friendly approaches to item analysis and test quality monitoring. The spreadsheet-generated indices behaved consistently with established psychometric expectations, confirming that this approach can provide meaningful insights into item functioning, test reliability, and student performance. The findings showed that teachers can use CTT and IRT indicators to identify weak items, refine distractors, adjust difficulty levels, and construct small-scale item banks aligned with competence-based assessment. These outcomes reinforce the pedagogical value of integrating psychometric reasoning into everyday assessment practice, supporting more informed instructional decisions and more accurate interpretations of student learning. In doing so, the study contributes to the broader goals of Sustainable Development Goal 4 (SDG 4), which emphasises inclusive, equitable, and high-quality education for all, as well as the need to strengthen assessment systems that support learning and fairness [60]. The study also highlighted important methodological considerations. While spreadsheets can effectively compute CTT indices and illustrate IRT models, the estimation of 2PL and 3PL parameters requires larger datasets and specialised software to ensure stable and interpretable results. The use of simulated data further limits the ecological validity of the findings, underscoring the need for future research using real classroom datasets. Despite these limitations, the study achieved its objectives: it provided a replicable analytical framework, demonstrated the interpretability of spreadsheet-based psychometrics, and illustrated how such analyses can support competence-based assessment. Future work should explore automation within spreadsheet environments, the integration of artificial intelligence for adaptive assessment, and the feasibility of applying spreadsheets to larger datasets and more complex IRT models. Overall, the study contributes to bridging the gap between psychometric theory and classroom practice, offering teachers a practical and transparent method for improving the quality of their assessment instruments while supporting the global agenda for high-quality education embodied in SDG 4.