Function Problems

How to Create an Excel Formula to Return Pass or Fail Based on Subject Scores

Adam Davis Oct 1, 2026 873 views

Question details

The user needs an Excel formula to determine a 'Pass' or 'Fail' status based on multiple complex grading conditions across varying subject scores.

How to Create an Excel Formula to Return Pass or Fail Based on Subject Scores

Product: Excel
Device & OS: not provided
Scenario: Calculating student grades where specific subjects and combinations of remaining subjects have different minimum passing score requirements.
Observed behavior: Requires a dynamic array formula to evaluate a mandatory subject score (>=40), the top two of the remaining subjects (>=40), and at least three of the other subjects (>=30).

Before you start

Ensure your version of Excel or WPS Spreadsheet supports dynamic array functions like TAKE and SORT, typically available in Office 365 or newer spreadsheet versions.

Solution 1Recommended

Use Dynamic Array Functions (TAKE, SORT, LARGE)

Utilize modern Excel array functions to dynamically sort and extract the required subject scores to evaluate the pass/fail conditions seamlessly.

This formula combines IF, AND, TAKE, SORT, and LARGE functions to evaluate all conditions simultaneously.

It checks if K8 is >= 40, sorts the range D8:J8 to ensure the top 2 subjects are >= 40, and verifies that the 3rd highest of the remaining subjects is >= 30.

Select the target cell

Click on the cell where you want the 'Pass' or 'Fail' result to appear for the first student.

Enter the evaluation formula

Type or paste the following formula into the formula bar: =IF(AND(K8>=40,AND(TAKE(SORT(D8:J8,1,-1,TRUE),,2)>=40),LARGE(TAKE(SORT(D8:J8,1,1,TRUE),,COLUMNS(D8:J8)-2),3)>=30),"Pass","Fail")

Apply and adjust ranges

Press Enter to apply the formula. If your scores are stored in a different range, manually adjust the cell references (K8 and D8:J8) to match your data.

Use Dynamic Array Functions (TAKE, SORT, LARGE)

Compatibility Note: If you receive a #NAME? error, your spreadsheet software likely does not support the TAKE or SORT functions. Upgrading your software or using helper columns for sorting is required.

Advanced Spreadsheet Tool

Easily Calculate Complex Grades with WPS Spreadsheet

WPS Office provides powerful data analysis tools and supports a wide range of advanced array formulas, making it easy to calculate complex grading criteria efficiently.

1. Open your workbook: Launch WPS Spreadsheet and open the gradebook file containing your student scores.
2. Input the formula: Click on the target cell and input the complex IF and AND formula to evaluate the passing conditions.
3. Apply to all rows: Press Enter, then drag the fill handle down to instantly apply the pass/fail grading logic to all students in the class.

Fully supports advanced dynamic array functions like SORT, TAKE, and LARGE.Highly compatible with Microsoft Excel formulas and file formats (.xlsx).Free to use with a lightweight, intuitive interface designed for seamless data management.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my pass/fail formula return a #NAME? error?

This error occurs if your spreadsheet software does not support modern dynamic array functions like TAKE and SORT. You will need to upgrade to a newer version of Excel or WPS Office, or use alternative legacy functions and helper columns.

How can I change the minimum passing score in the formula?

You can change the passing threshold by directly modifying the numbers in the formula string. For example, change '>=40' to '>=50' to increase the required minimum score for those specific subjects.

Can I use this pass or fail formula for a different number of subjects?

Yes. However, you must adjust the range references (like D8:J8) and modify the column parameters in the TAKE and LARGE functions to correctly match the total number of subjects in your dataset.

Excel & Data Problems