Logical FunctionsAdvanced

Screen Mortgage Pre-Approvals by Wrapping AND Inside OR

OR gives an applicant two separate paths to automatic approval, and nesting AND inside the second path lets that one path demand two conditions at once without IF needing a third branch to hold it.

Task:

You screen mortgage pre-approval applications for a credit union. Underwriting guidelines approve an applicant automatically if their credit score is 740 or higher. Below that, an applicant still qualifies if their credit score is at least 680 and their debt-to-income ratio is 36% or under — both of those have to hold at once. Anyone who clears neither path gets referred to a human underwriter instead of an automatic decision. In column D, use IF together with OR and AND to mark each applicant "Approved" or "Needs Review". Fill in rows 2 through 7.

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.

7 rows × 4 columns6 cells you fill in
ABCD
1ApplicantCredit ScoreDTI %Decision
2Priya Shah76042
3Carlos Mendes70030
4Latoya Fields69540
5Dmitri Volkov65020
6Hannah Osei74550
7Jian Wu68036
What this exercise teachesMay contain the answer

OR(B2>=740,AND(B2>=680,C2<=36)) gives Priya Shah's row nothing to weigh beyond her own 760 credit score — it already clears the first argument, so OR returns TRUE before it ever has to look at her 42% DTI, which is why a debt load that bad still comes back "Approved". Carlos Mendes has no such shortcut: his 700 fails the first argument outright, so OR has to fall back on the AND, and 700>=680 together with 30<=36 both hold, so that AND is TRUE and OR passes on the second route instead. Latoya Fields shows what happens when neither route survives: 695 fails the first argument, and inside the AND her 695 clears 680 but her 40% DTI fails the 36 limit, so the AND returns FALSE too and OR has nothing left to approve — the same shape that sends Dmitri Volkov's 650 to "Needs Review", since his AND fails on the credit score itself. Jian Wu's row is the one that would break a formula written with > and < instead of >= and <=: 680 and 36 don't beat the thresholds, they sit exactly on them, and only a formula built with "at least" and "or under" in mind returns TRUE for a route the business rule meant to include.