To solve the problem of returning ‘pass’ when two cell values are within a specified range, ‘fail’ otherwise, and leaving it blank if either cell is empty, we can use Excel’s IF function with nested logical functions. Here’s how to approach it:
-
Check for Empty Cells: Use the IF function combined with OR(ISBLANK(cell1), ISBLANK(cell2)) to determine if either of the two cells is empty. If so, return a blank value.
-
Range Check: If neither cell is empty, use nested IF functions to check whether both cell values fall within the specified range (e.g., between 10 and 20). Use AND(cell >= lower_limit, cell <= upper_limit) for each cell to ensure they are within bounds.
-
Return Result: If both cells meet the range criteria, return ‘pass’. Otherwise, return ‘fail’.
The final formula combines these checks:
|
1 2 |
=IF(OR(ISBLANK(A1), ISBLANK(B1)), “”, IF(AND(A1>=10, A1<=20, B1>=10, B1<=20), “pass”, “fail”)) |
This formula ensures that:
– If either cell is empty, the result is blank.
– If both cells are within the range, it returns ‘pass’.
– Otherwise, it returns ‘fail’.