Logical FunctionsAdvanced

Decide who qualifies for tuition reimbursement from two rules that both have to clear

AND only turns TRUE when every argument inside it does, and one of those arguments here is itself an OR — so a request can satisfy either of two routes and still has to clear the tenure rule on top.

Task:

You run tuition reimbursement requests for HR at a mid-size firm. The policy: an employee qualifies if they have been employed at least 12 months, and the course is either job-related or was pre-approved by their manager — tenure is never waived, but either of the other two is enough on its own. In E2, work out each request in column E as "Eligible" or "Not eligible", then copy down to row 6.

Interactive Spreadsheet

Hints

+5 XPno-hints bonus

Solve it on your own to keep the bonus. Each hint gets one step closer to the formula.

The data in this exercise

This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.

6 rows × 5 columns5 cells you fill in
ABCDE
1EmployeeMonths employedJob-relatedManager pre-approvedEligible
2Alders18YesNo
3Brandt6YesYes
4Corwin24NoNo
5Delgado15NoYes
6Ekstrom9NoYes
What this exercise teachesMay contain the answer

AND only returns TRUE when both of its arguments do, and its second argument here is OR(C2="Yes",D2="Yes") — which is itself TRUE the moment either half is. That is why Brandt, six months in with both the job-related and pre-approved boxes checked, still comes back Not eligible: the OR clears easily, but AND's other argument, the tenure check, does not, and AND cannot pass on one true argument alone. Delgado is the mirror case — a course that is neither job-related nor pre-approved by itself, but fifteen months of tenure plus manager approval is enough to clear the OR, and with tenure already satisfied that is all AND needs. Flattening this into a single OR of three conditions would have reimbursed Brandt's course on day one, which is exactly what the tenure rule exists to prevent.