Categorizing Records with IF Logic

Hello. In the previous lesson, you calculated row-level metrics such as revenue, cost, and profit, then summarized them with functions such as SUM and AVERAGE. Those calculations describe how much happened. Often, the next business need is to decide what each record means: Which expenses need review? Which orders count as high value? Which salespeople met target?

This lesson introduces Excel’s IF function, which applies a stated business rule consistently to every row and returns a useful category. You will learn to write, copy, inspect, and validate an IF formula—an essential step before later summarizing categories with conditional functions such as COUNTIFS and SUMIFS.

---

From a business rule to an Excel formula

A business rule is a clear condition that determines an action, label, or category. For example:

• An expense is Over Budget when actual spending exceeds budgeted spending.
• An order is High Value when revenue is at least 1,000.
• A salesperson is On Target when sales meet or exceed their target.
• An invoice is Needs Follow-up when it remains unpaid after its due date.

Each rule has three parts:

1. A condition to test.
2. A result if the condition is true.
3. A result if the condition is false.

Excel’s IF function encodes exactly that structure:

=IF(logical_test, value_if_true, value_if_false)

Read it as a sentence:

If this test is true, return this result; otherwise, return that result.

For a budget worksheet with budgeted spending in column B and actual spending in column C, an appropriate status formula in D2 is:

=IF(C2>B2,"Over Budget","Within Budget")

Excel first evaluates the test C2>B2.

• If actual spending is greater than budgeted spending, the test is TRUE, so Excel returns Over Budget.
• If actual spending is equal to or less than budgeted spending, the test is FALSE, so Excel returns Within Budget.

Notice a subtle but important decision in this rule: an amount exactly equal to budget is classified as Within Budget. If company policy instead says that spending must be below budget, the test and labels should reflect that policy. The formula is only as sound as the business rule it represents.

---

Logical tests: the decision inside IF

The first argument of IF is the logical test. It must evaluate to either TRUE or FALSE.

You can test a condition directly in a cell. For example:

=C2>B2

If C2 contains 921.58 and B2 contains 800, Excel returns TRUE. That test is the decision-making core of the earlier IF formula.

The most useful comparison operators are:

Business question	Logical test example	Meaning
Is actual spending over budget?	C2>B2	Actual is greater than budget
Did sales meet the target?	D2>=E2	Sales are greater than or equal to target
Is an order below a review threshold?	F2<500	Order value is less than 500
Is a status approved?	G2="Approved"	Text matches “Approved”
Is a customer not in the active segment?	H2<>"Active"	Text does not match “Active”
Is an amount zero or negative?	I2<=0	Value is less than or equal to zero

Two syntax rules prevent common errors:

• Text must be enclosed in double quotation marks. Write "On Target" or "Approved".
• Numbers and cell references are not enclosed in quotation marks. Write >=1000, not >="1000".

A comparison operator defines the boundary of the category. Consider a sales target of 10,000:

=IF(D2>=10000,"On Target","Below Target")

Using >= means sales of exactly 10,000 meet the target. If you used > instead, a salesperson with exactly 10,000 would be incorrectly labelled Below Target under the stated policy.

Before writing an IF, say the rule in ordinary language and pay particular attention to boundary words:

• “More than” usually means >.
• “At least” usually means >=.
• “Less than” means <.
• “At most” usually means <=.
• “Exactly” means =.

---

Building useful categories from transaction data

Suppose an order table has already calculated Revenue in column E.

Order ID	Customer	Revenue	Value Segment
ORD-1001	Northstar Ltd	1,250	—
ORD-1002	Bramble Co	670	—
ORD-1003	Greenline Inc	1,000	—

Management defines a high-value order as one with revenue of 1,000 or more. In F2, enter:

=IF(E2>=1000,"High Value","Standard")

The resulting categories are:

Order ID	Revenue	Value Segment
ORD-1001	1,250	High Value
ORD-1002	670	Standard
ORD-1003	1,000	High Value

The formula makes the threshold operational. It also makes the classification consistent: every order is assessed according to exactly the same criterion.

Copying the formula correctly

Once the formula works in the first data row, copy it down the category column using the fill handle or by double-clicking the small square at the selected cell’s lower-right corner.

In the next row, Excel changes:

=IF(E2>=1000,"High Value","Standard")

to:

=IF(E3>=1000,"High Value","Standard")

This is the intended behavior. E2 is a relative reference, so each row evaluates its own revenue.

Inspect at least one copied formula rather than assuming the fill worked correctly. A category label may look plausible even when a reference points to the wrong cell.

---

Make business rules maintainable with fixed criteria cells

Hard-coding a threshold such as 1000 is acceptable for a quick analysis, but it is not ideal for a reusable report. Business definitions change. A manager may redefine a high-value order as 1,500, or rename the category Key Account.

A more maintainable worksheet places assumptions in labelled cells:

Cell	Label	Value
H2	High-value threshold	1,000
H3	High category label	High Value
H4	Standard category label	Standard

Then the formula in F2 becomes:

=IF(E2>=$H$2,$H$3,$H$4)

This formula uses two reference behaviors:

• E2 is relative because each row must test its own revenue.
• $H$2, $H$3, and $H$4 are absolute because every row must use the same approved threshold and labels.

This structure has practical benefits:

• Update the threshold once rather than editing many formulas.
• Make the policy visible to someone reviewing the worksheet.
• Reduce the risk of formulas using inconsistent criteria.
• Make the output easier to explain in an interview or handover.

A concise analyst-style explanation might be:

Orders were categorized as High Value when revenue was at least the approved threshold in cell H2; all other records were classified as Standard. The rule is reference-based, so changes to the threshold update all classifications automatically.

---

IF can return labels, numbers, or calculations

The most common use at this stage is returning readable labels. However, the true and false outcomes are not limited to text.

Return a numeric flag

A 1 or 0 flag can support later calculations:

=IF(E2>=$H$2,1,0)

This returns 1 for a high-value order and 0 otherwise. A label is usually easier for people to read; a numeric flag can be convenient for certain calculations. Clearly name the column so a reader knows what 1 and 0 mean.

Return a conditional calculation

For a budget table, suppose Budget is in B2 and Actual is in C2. To calculate the overspend amount while returning zero for on-budget records:

=IF(C2>B2,C2-B2,0)

The formula only subtracts when actual spending is over budget. If spending is within budget, it returns 0.

This complements the category formula rather than replacing it:

Column	Formula purpose
Status	Identify whether the record is over budget
Amount Over	Quantify the amount requiring attention

A strong worksheet separates those ideas. Status answers “which records?”; Amount Over answers “how much?”

---

Treat blanks deliberately

IF always follows the rule you give it, even if the underlying data is incomplete. That means a blank value can be misleading.

For example, suppose revenue is blank in E2 and you use:

=IF(E2>=$H$2,"High Value","Standard")

Excel may classify that row as Standard, even though the appropriate interpretation is “revenue missing.” A missing value is not evidence that an order is standard.

If blank values are possible, flag them explicitly:

=IF(E2="","Missing Revenue",IF(E2>=$H$2,"High Value","Standard"))

This formula contains an IF inside another IF, known as a nested IF. For now, focus on its logic rather than treating nesting as a new topic:

1. Check whether Revenue is blank.
2. If it is blank, return Missing Revenue.
3. Otherwise, apply the normal high-value rule.

Use this only when the status of missing data genuinely matters. Do not hide incomplete records behind a normal category.

---

A practical validation routine

After applying an IF formula down a column, validate both the formula and the business logic.

1. Check one row that should be true. For example, confirm that an order of 1,250 is labelled High Value when the threshold is 1,000.
2. Check one row that should be false. Confirm that an order of 670 is labelled Standard.
3. Check the boundary. If the rule says “at least 1,000,” verify that an order of exactly 1,000 is High Value.
4. Inspect a copied formula. Select a later row and confirm its row reference changed while fixed criteria references retained dollar signs.
5. Check for blanks and errors. Decide whether a blank input should receive a category, a missing-data label, or a separate review.
6. Read the labels as a stakeholder would. Use terms that communicate an action or business meaning, such as Needs Review, On Target, or Over Budget, rather than vague labels such as Group 1.

The important distinction is this: IF does not discover a meaningful category on its own. It applies the category definition you specify. An analyst must therefore document the rule, select accurate labels, and test edge cases before presenting the result.

---

Key takeaways and next step

The IF function turns a business rule into a consistent row-level category:

=IF(logical_test, value_if_true, value_if_false)

Remember to:

• Use a comparison such as >, >=, <, <=, =, or <> for the logical test.
• Put text outputs in double quotation marks.
• Define boundaries carefully—especially whether an exact target value should pass or fail.
• Use relative references for row-specific values and absolute references for shared thresholds or labels.
• Treat blank values intentionally instead of allowing them to fall silently into a normal category.
• Validate true cases, false cases, boundary cases, and copied formulas.

Next, you will use SUMIFS and COUNTIFS to calculate totals and counts for categories such as High Value, Over Budget, or On Target—turning the labels created here into summary metrics.