IF in Excel: thresholds, outcomes and missing data

You have a target of 100 units. One cell holds a result, and the next should say whether the target was reached. You could type the labels manually, but every change would require remembering to update a second cell. I prefer to write the rule with IF and let the spreadsheet select the outcome.

The difficult decisions often come before the formula. Does exactly 100 qualify? What should happen when a result is missing? Should the output be a number or a description? I will work through these questions using a small synthetic table and keep the focus on a clear two-outcome decision.

IF chooses between two results

IF checks a condition and returns one result for TRUE and another for FALSE. This is the behavior described in Microsoft’s function reference.

=IF(logical_test,value_if_true,value_if_false)

I use English names and commas. In Polish Excel the function is JEŻELI; argument separators depend on regional settings. A basic IF decision does not require the newest dynamic array features.

Start by entering the test alone. =B2>=100 gives TRUE or FALSE. Once that matches the intended rule, wrap it in IF. Otherwise, a polished label can conceal a mistaken condition. The formula cannot decide whether the target includes its boundary; the author must make that choice.

I also make both outcomes explicit. Leaving an argument out can produce a result that the reader did not expect. For a learning example, clarity is more useful than saving a few characters.

Three results and one threshold

The data below is synthetic. Put the headers in A1:B1 and the records in rows 2:4. Results are numbers, without the word “units” typed into them. Enter the target 100 in E2.

In C2 I write:

=IF(B2>=$E$2,"Target reached","Below target")

Copy the formula down to C4. Ada has 90 and receives “Below target”. Ben has exactly 100 and receives “Target reached”. Celina has 120 and also qualifies. If I used > instead of >=, Ben would fall into the other group.

The dollar signs keep the threshold in E2 while the formula is copied. B2 must become B3 and B4 because each row describes another person. Microsoft explains relative and absolute references. Fixing both references would make every status describe Ada.

I always inspect a result below the threshold, one exactly on it and one above it. A sample containing only 120 and 150 would not reveal a mistake between greater than and greater than or equal to. The boundary case is usually the cheapest way to discover an ambiguous rule.

Text in quotes, numbers without quotes

An IF result can be a calculation. Consider an entirely synthetic points rule: a result of at least 100 earns additional points equal to ten percent of the result; otherwise it earns zero.

=IF(B2>=$E$2,B2*10%,0)

Ada receives 0, Ben 10 and Celina 12. This is a calculation exercise with no connection to a real compensation policy. Its purpose is to show the difference between a numerical result and a label.

I do not write "0" when I want the number zero. Quotes turn that into text. Likewise, "B2*10%" is a string, while B2*10% performs the multiplication. Microsoft’s IF reference explains the role of quotes around textual outcomes.

I prefer consistent output types. A status column contains text; a points column contains numbers. Mixing amounts with a message such as “missing” makes subsequent processing harder. If a report needs an explanation of a problem, a separate quality column is usually easier to maintain.

This separation also helps the person using the workbook. They can filter status labels without accidentally treating a descriptive message as part of the measure. A useful spreadsheet should reveal what each column means before anyone opens the formula bar.

Missing results need their own rule

Add a fourth person in row 5 and leave B5 empty. Does that mean no measurement or a measured zero? In this example, I want a separate “Missing data” status rather than automatically calling it a missed target.

=IF(B2="","Missing data",IF(B2>=$E$2,"Target reached","Below target"))

After filling down, row 5 receives the separate status. The first test catches an empty cell and an empty string "". A cell containing a space is not an empty string. Imported files can contain these apparently blank values, so the distinction matters.

If I need to check that a cell contains neither a value nor a formula, I use ISBLANK. A formula returning "" does not make its cell genuinely empty. Both approaches are covered in Microsoft’s guide to checking blank cells.

Returning "" from IF can hide the output visually, but the formula still occupies the cell. I do not describe that as deleting data. In a report where missing results affect interpretation, a visible explanation is often more useful than a field that merely looks empty.

Check imported types before classifying them

When column B comes from an export, I first confirm its types. A number stored as text can produce a misleading comparison with a numerical threshold. Similar cell appearance does not establish that the inputs are equivalent.

ISNUMBER can check the type. This version returns a message for nonnumeric inputs:

=IF(ISNUMBER(B2),IF(B2>=$E$2,"Target reached","Below target"),"Check data")

It intentionally treats an empty cell as an input requiring inspection too. If a missing measurement needs its own label, retain the earlier blank check as well. The Microsoft IS functions reference documents these information functions.

ISNUMBER checks that a value is numerical, not that it makes business sense. A negative unit count or a wildly implausible result may still require a separate rule. Data type and data quality are related but distinct questions.

I avoid hiding every problem by replacing an error with zero. A broken reference, a failed import and a missing measurement call for different responses. Decide which situations are acceptable before choosing a fallback value that makes the report look tidy.

Multiple conditions and nesting limits

Suppose reaching the target also requires Approved in D2. I combine the conditions with AND:

=IF(AND(B2>=$E$2,D2="Approved"),"Target reached","Does not meet rule")

The number alone is no longer enough. When alternative conditions should produce the same outcome, use OR. I explain that distinction in the AND and OR guide so that this article can stay focused on IF’s decision.

You can place another IF inside an outcome, as in the missing-data example. With multiple thresholds, evaluation order becomes part of the rule. If the first condition handles every result from 100 upward, a later condition intended for results from 120 upward may never determine the label.

For a longer sequence, consider IFS or a threshold table with an appropriate lookup function. IFS returns the outcome for the first true condition, so the order still needs thought. A different function name will not organize an ambiguous policy.

I also ask whether someone else can read the formula without tracing a forest of brackets. A helper column can make one decision visible at a time. That additional column can be a good trade when the alternative is a compact expression that nobody wants to edit.

Exercise: change the rule before the formula

Set E2 to 120. In the first example, Ada and Ben should be below target while Celina qualifies. Then change the rule to “strictly above 120”. Celina should no longer qualify either. The sentence changed meaning, so the comparison operator must change too.

Add an empty result and a number stored as text. Decide whether both receive “Check data” or separate labels. Write those choices above the table so another reader can understand the statuses without inspecting the formula.

For a total across many transactions, move on to SUMIFS. IF works well as a decision for one row. Choosing the measure and defining trustworthy inputs remain part of the analyst’s job.