IF function in Excel

So far every formula computes one arithmetic result. Real analysis needs formulas that decide: one rate above a threshold, another below, a flag when two conditions both hold, a label for the exceptions. That's Boolean logic, and it rests on a single primitive, the comparison. A comparison such as B2>=600000 evaluates to one of two values, TRUE or FALSE. Everything else is built on routing behaviour off those two values.

How do IF and IFS work?

IF is the atom. Its shape is IF(logical_test, value_if_true, value_if_false). =IF(B2>=600000, "Qualifies", "No") returns the text Qualifies when the sales figure in B2 is at least 600,000 and No otherwise. The test can be any expression that resolves to TRUE/FALSE. The two result arguments can be numbers, text, or other formulas, including other IFs, and that's where nesting begins.

IFS is the clean multi-branch. When you have several thresholds (a tiered commission, a grade band, a rating scale), nesting IFs inside IFs works but quickly becomes unreadable. IFS tests conditions in order and returns the result of the first one that's TRUE:

=IFS(B2>=1000000, 0.12, B2>=600000, 0.08, B2>=300000, 0.05, TRUE, 0)

Read left to right. If sales are at least 1,000,000 the rate is 12%. Else if they are at least 600,000, it's 8%. Else if at least 300,000, 5%. Else it's 0%, because the literal TRUE is a catch-all that's always true, so it fires when nothing above did.

Order matters absolutely. IFS stops at the first hit, so you must list thresholds from most to least restrictive. Reverse them, putting B2>=300000 first, and everyone at 5% or above is captured by the loosest band and the higher tiers never fire. And always end with a TRUE catch-all, because an IFS where no condition matches returns the error #N/A, which is rarely what you want. Version note: IFS needs Excel 2019 or later, or Microsoft 365. In older versions you nest IFs or use a lookup table instead.

How do AND, OR and NOT combine tests?

AND, OR, NOT combine tests. These take one or more logical tests and collapse them to a single TRUE/FALSE:

• AND(test1, test2, …) is TRUE only if every test is true. Take bonus eligibility that requires both a sales floor and a quota, meaning sales of at least 600,000 and quota attainment of at least 90%: =IF(AND(B2>=600000, C2>=0.9), "Eligible", "No").
• OR(test1, test2, …) is TRUE if any test is true. =IF(OR(D2="North", D2="East"), "Priority region", "Standard").
• NOT(test) flips a single result, so NOT(TRUE) is FALSE. It's often paired with an information function. =IF(NOT(ISBLANK(B2)), B2*CommRate, 0) pays commission only on rows where a sales figure is actually present.

One deeper idea is worth planting now, because it pays off across the whole track. In Excel, TRUE behaves as 1 and FALSE as 0 the moment you do arithmetic on them. So =(B2>=600000)*1 returns 1 or 0, and =(5>3)+(2>9) returns 1, one true comparison plus one false. This "Boolean arithmetic" is the seed of the criteria-counting you'll do with SUMPRODUCT and the logic inside SUMIFS later in the track. It also explains why analysts sometimes multiply conditions together instead of nesting AND. (cond1)*(cond2) is 1 only when both are true, the same as AND, but it works element-by-element across a whole range, which AND doesn't. You don't need that today. Recognising it now means it won't surprise you later.