Logical
Check if all values in a range are at least a minimum threshold
Verify that every cell in a batch meets a minimum score AND no cell was left blank—avoiding the MIN(range) blank-cell loophole.
Why MIN(A2:A5) >= 75 passes incomplete batches
MIN ignores empty cells and text! If a trainee skips Module 3 (leaving A4 blank) and scores 84, 91, and 88 on the others, MIN(A2:A5) returns 84 >= 75 = TRUE—certifying an incomplete exam!
Requiring both COUNT(A2:A5) = ROWS(A2:A5) (every slot holds a number) and COUNTIF(A2:A5, "<" & B2) = 0 (zero scores fall below PassFloor) closes the blank-cell loophole.
This completeness check is for one column. For a rectangular range compare COUNT with ROWS(range)*COLUMNS(range), and validate that the threshold is numeric. Numeric-looking text is not a completed numeric score.
| Scenario | Scores (A2:A5) | MIN >= 75 | Complete + COUNTIF Pass |
|---|---|---|---|
| All >= 75 | 84, 91, 75, 88 | TRUE | TRUE |
| One < 75 | 84, 91, 72, 88 | FALSE | FALSE |
| Missing score | 84, 91, (blank), 88 | TRUE (bug!) | FALSE |
Completeness-checked threshold formula
An exact boundary score of 75 satisfies >= 75 because "<75" counts strictly lower values (0 failures).
=AND(COUNT(A2:A5) = ROWS(A2:A5), COUNTIF(A2:A5, "<" & B2) = 0)Demonstrate the blank-cell trap in plain MIN
Clearing A4 makes MIN(A2:A5) >= B2 return TRUE while the completeness-checked formula returns FALSE.
=MIN(A2:A5) >= B2Try the example
Use the original values or change them to test your understanding.