Excel Formula to Return FAIL When Fewer Than Six Marks Meet a Threshold
Question details
The user needs a conditional formula to evaluate a range of marks, sum the top scores if conditions are met, and return FAIL if fewer than six marks meet a specific minimum or maximum threshold.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Calculating complex student grades or performance scores where a minimum number of qualifying marks is strictly required to pass.
- Observed behavior
- Requires combining logical functions to accurately count qualifying marks, extract the best values from an array, and output either a text result (FAIL) or the final calculated score.
Ensure your data ranges contain actual numeric values and do not contain hidden text strings or error values, as these will interfere with the COUNTIF, LARGE, and SMALL functions.
Calculate Based on Higher Scores (Minimum Threshold)
Use this formula combination when higher marks are better and a minimum score is required to pass the evaluation.
By nesting the COUNTIF function inside an IF statement, you can verify if the required number of marks exists before performing the final calculation using the SUM and LARGE functions.
Click on the cell where you want the final calculated result or the FAIL text to appear.
Type the formula: =IF(COUNTIF(G12:G20,">=39")<6,"FAIL",SUM(LARGE(G12:G20,{1,2,3,4,5}))+G11). Ensure you adjust the range (G12:G20) and the threshold value (39) to match your exact grading criteria.
Press the Enter key. If fewer than six marks in the specified range are 39 or higher, the cell will display FAIL. Otherwise, it will sum the top 5 marks from the array and add the value from cell G11.
Calculate Based on Lower Scores (Maximum Threshold)
Use this alternative method for scoring systems where lower values represent better performance, such as ranking systems or race times.
Calculate Complex Conditional Formulas Easily with WPS Spreadsheet
WPS Spreadsheet provides robust support for advanced array functions, statistical formulas, and logical operations, allowing you to build complex grading and threshold calculators with ease.
- 1. Open your dataset: Launch WPS Spreadsheet and open the workbook containing your scoring data.
- 2. Input the formula: Select the target cell and type your combined IF and COUNTIF formula using the top formula bar.
- 3. Drag to fill: If you are calculating this for multiple subjects or students, click and hold the fill handle at the bottom-right of the cell, then drag downward to automatically apply the formula to other rows.
Frequently Asked Questions
Why does my LARGE function return a #NUM! error?
The #NUM! error occurs in the LARGE or SMALL function if the specified array is completely empty, or if you request a position (like the 5th value) that is greater than the total number of data points available in your range.
Can I adjust the formula to sum exactly 6 marks directly from the array?
Yes, if you want to sum the top 6 marks directly from the range without adding an external cell (like G11), change the array constant to sum six items: SUM(LARGE(G12:G20,{1,2,3,4,5,6})).
How does the array constant {1,2,3,4,5} work in this formula?
The array constant enclosed in curly brackets forces the LARGE or SMALL function to return an array of multiple specific positions (the 1st through 5th highest or lowest values). The SUM function wrapper then adds all these extracted values together into a single total.
Is there a way to make the pass threshold dynamic instead of hardcoding it?
Yes, you can reference another cell containing your threshold limit. Instead of typing ">=39" inside the COUNTIF function, use ">="&A1, where cell A1 contains the numeric threshold value you want to test against.
