IF Formula Builder

Somewhere right now, someone is staring at =IF(A2>=90,"A",IF(A2>=80,"B",IF(A2>=70,"C",IF(A2>=60,"D""F")))) and trying to find the mistake by squinting. The mistake is a comma that should exist and doesn't. The symptom is that the whole formula highlights in the formula bar with the specific color of doom, and the error dialog's best guess is a paragraph of unhelpful. There are four closing parentheses at the end and the person has counted them three times, losing track on the third pass each time.

IF is the most-used conditional function in Excel by an enormous margin, and nested IF — an IF inside an IF inside an IF, which is what any multi-tier rule requires — is where the trouble starts. The logic is easy. The bookkeeping is not: every level adds one open parenthesis at the front, one close at the back, and a hundred characters of tape between you and the bug you're hunting. Excel forums have run the same haunted thread on this for decades: a pasted 400-character formula, one delimiter short of valid, the sheet now reading #NAME? or nothing at all.

This builder exists so that thread never starts. You describe the rule as visible rows — value, comparison, result — and the tool compiles those rows into a correctly-parenthesized nested IF (or a flat IFS, your choice), live-previews it against sample data, and hands you the formula with a copy button. The parentheses are guaranteed because you never typed them. The order of evaluation is guaranteed because the rows evaluate top to bottom and you can see the waterfall.

The interface asks you the question in the shape you already hold it in your head. A grading policy is not syntax — it's "at or above 90, an A; at or above 80, a B; at or above 70, a C..." So the builder gives you rows: threshold, operator, result. Add a row per tier. Reorder them with a drag if you decide the cutoffs go the other way. Set the final fallback — what happens when nothing matches — in its own field, so "F" and "9.95" and "review" are first-class inputs instead of the last argument in a chain you're afraid to touch.

Underneath, the compiler does the mechanical work that generates the errors when humans do it: it orders the comparisons, emits the nested calls with balanced parentheses, quotes the text results, leaves the numbers bare, and closes every level in the right place. Then it renders the formula two ways — classic nested IF for every Excel version since the 1990s, and IFS for Excel 2019, Microsoft 365, and Google Sheets, where the flat form reads better. A Google Sheets variant ships alongside, because hybrid shops are the norm now and nobody should hand-edit dialect differences at 4 p.m.

The part that earns its keep is the preview grid. Type a handful of representative values — the boundary cases, the weird ones, the one you're worried about — and watch the compiled formula classify each row live. You see the rule run before it touches a real workbook. In our testing this is where most bugs die: not in the generated syntax, which is always valid, but in the policy itself, where the preview shows 640 landing in the wrong tier and the fix is one drag of a row.

1. 1

   Point at the cell

   Enter the input reference — A2, or Score[@Value], or whatever cell holds the value being classified. The builder uses it in every condition row.

2. 2

   Add your condition rows

   Each row is a threshold, an operator (=, <>, >, <, >=, <=), and the result when it passes. Start with the most restrictive condition at the top — that ordering is what makes the funnel correct.

3. 3

   Set the fallback

   The final result when no row matches. Omitting it in raw IFS yields #N/A; the builder always asks, so the fallback is a decision instead of an accident.

4. 4

   Choose the output form

   Nested IF (maximum compatibility) or IFS (cleaner reading in modern Excel and Sheets). Both are generated from the same rows, so switching costs nothing.

5. 5

   Test in the preview grid

   Enter sample values including every boundary — 90 and 89.9, not just 95. Each row shows the formula's result beside the input, updated as you adjust the rule.

6. 6

   Copy into the workbook

   One click copies the formula with proper quoting for Excel or the Sheets variant. Absolute references toggle with a checkbox when you plan to fill down a column.

The function signature is IF(logical_test, value_if_true, [value_if_false]), and each slot has rules worth knowing precisely. logical_test is any expression that resolves to TRUE or FALSE: comparisons like A2>=90, and-combinations like AND(A2>=90, B2<3), or a bare cell reference holding a boolean. A quirk inherited from spreadsheet history: numbers count as conditions too — zero is FALSE, any other number is TRUE — so IF(A2, ...) on a cell of 0 is testing "is nonzero" whether you meant to or not.

value_if_true and value_if_false accept anything a formula can return: constants, references, whole expressions. The laziness matters and is underappreciated: Excel evaluates only the branch it takes. IF(TRUE, 1, 1/0) returns 1 with no division error, because the false branch never runs. People wrap both branches in error handling they don't need; the flip side is that an error inside the taken branch surfaces immediately, so IF(A2>0, 100/A2, "n/a") is already the correct guard pattern for division.

Nesting is just a value slot containing another IF. Reading =IF(A2>=90,"A",IF(A2>=80,"B","C")) out loud: if at least 90, A; otherwise (which already means below 90), if at least 80, B; otherwise C. Every inner level inherits the failures of the outer levels — that's the waterfall, and it's why the descending-threshold trick works. The outer IF has already established "below 90" by the time the inner one checks 80, so the inner check never needs to say "and also below 90." When people write redundant ranges like AND(A2>=80, A2<90) inside every level, the formula still works; it's just four times longer than the logic requires.

A little history explains why two functions coexist. Through Excel 2003, nested IFs hit a wall at seven levels — a real constraint with real victims: anyone with more than seven price bands, grade tiers, or status categories had to invent workarounds, usually helper columns or the beginnings of a lookup table. Excel 2007 raised the limit to 64, which solved the arithmetic and not the problem, because a 40-level nested IF is technically legal and humanly unmaintainable. Sixty-four floors of building is not the same as a building you can live in.

IFS arrived in 2019 (Excel 2019 perpetual and Microsoft 365) as the official flat form: IFS(test1, result1, test2, result2, ...) evaluates pairs left to right and returns the first passing result. No parentheses pyramid, no closing-parenthesis counting, and each tier is one visible pair instead of one more indentation. The gap it leaves: no default. If every test fails, IFS returns #N/A — so well-built IFS formulas end with the pair TRUE, "fallback", an explicit catch-all the reader can see. Google Sheets, which had IFS slightly earlier, plays by the same rule.

The builder emits both because both belong in circulation. Nested IF is the compatibility floor — it runs in a 2010 workbook someone emails you, which still happens more often than any of us enjoy. IFS is what you write when the whole shop is on modern versions. And the third escape — rewriting the whole rule as a lookup table with approximate-match VLOOKUP or XLOOKUP — is the eventual destination for tier logic that keeps growing, a move we cover in the comparison below because it genuinely outclasses both formulas past a certain size.

The examples below are real output from the builder's compiler, and the result columns are verifiable by hand — which is the point of the preview grid. Rule one is the classic: letter grades from scores.

Notice the IFS version's final pair — TRUE, "F" — the explicit catch-all that keeps unmatched scores from becoming #N/A. The builder inserts it whenever you set a fallback, which is every time, because the alternative is an error the first time a 59 walks through.

That second formula is the honest advertisement for the preview grid. It is syntactically flawless. It produces correct-looking output for 90% of test values. The only values that expose it are the ones above the highest threshold — which is precisely the case most people don't think to type. Boundary testing is not a formality here; it's the whole review, and the grid makes it a reflex.

• Numbers stored as text. A cell displaying 90 but holding the text "90" fails A2>=90 in the usual surprising direction — text compares against numbers by type ordering, not value, and any text sorts above any number. The preview grid surfaces this instantly (the result is wrong for no visible reason), and fixing the source data or wrapping VALUE() around the input resolves it.
• Case-insensitivity bites codes. IF(A2="YES",...) accepts yEs, and for a status column that's forgiving; for a SKU column where AB-1 and ab-1 are different products, it's a silent data-corruption bug. EXACT(A2,"AB-1") is the strict version.
• The empty-string trap. Returning "" looks blank but isn't: ISBLANK returns FALSE, COUNTA counts it, and downstream IFs testing A2="" behave differently than ones testing A2=0. If blank-ness matters downstream, prefer leaving the cell truly empty via IF(...,"",...) alternatives like returning NA() and filtering — or restructure so the branch returns a number.
• Locale separators. In Excel installations set to regions using comma-decimals, the argument separator is a semicolon — =IF(A2>=90;"A";"B") — and a formula pasted from a US tutorial errors immediately. The builder's output targets comma-separated dialects; if your Excel uses semicolons, the Excel Formula Translator converts separators and function names between dialects.
• Dates in conditions. A2>=DATE(2026,1,1) is robust; A2>="1/1/2026" compares text, not dates, and works only by accident of string ordering. Six months later, the accident stops. Always compare dates to dates.
• Volatility compounding. IF pairs badly with NOW() or RAND() in the condition: every recalculation re-rolls the branch, and a report that changes between two openings loses the room's trust. Freeze volatile inputs into cells first.
• Beyond six tiers, readability is the bug. The hard limit is 64; the maintainable limit arrives far earlier. When the rule stops fitting in one confident read, the lookup-table rewrite (below) is cheaper than another level of nesting.

The tool's audience is broader than the formula-literate, deliberately. Teachers building grade scales are the archetypal user: the policy is crystal clear in their head, the mechanics of nesting are not, and the difference between "an A" and "the A branch of a four-level conditional" is a barrier that should never have existed. The preview grid speaks their language — put the class's awkward scores in, see the letters come out.

Ops and finance people hit it with tier rules: shipping thresholds, commission bands, approval matrices ("under $500 auto-approve, under $5,000 manager, above that director"), discount ladders, tax-style brackets. Same structure every time — a handful of bands, a fallback, and consequences when the bands are off by one. The stall pattern is identical too: the first two levels get written confidently, the third requires actual parenthesis counting, and the fourth gets delegated to a column of hand-typed values instead. The builder removes the counting, and the hand-typing goes with it.

The third group is everyone supporting the first two. Spreadsheet auditors who need a six-tier rule explained, trainers teaching the concept, and analysts porting a rule from a policy document into a model. For that last group the row form is nearly a transcription exercise — policy documents are written in tiers, so the mapping is one-to-one, and the preview is the compliance check.

Four tools answer "classify this value," and choosing among them is a real decision, not a style preference:

The guidance we give people: nested IF below three tiers (it's the most compatible and reads fine small), IFS from four to about eight uniform tiers, SWITCH when the rule is exact-match categories rather than thresholds, and a lookup table the moment the tiers are owned by someone who changes their mind — a pricing table in a range beats a formula edit every single time, because the sales ops person who updates prices should not be opening the formula bar to do it. The XLOOKUP Formula Builder and VLOOKUP Formula Generator cover that final migration, and the gradient between IF and those tools is exactly why they're neighbors on this site.

One hybrid worth knowing: SWITCH handles the exact-match axis and IF handles the threshold axis when a rule genuinely has both — "status CLOSED → 0, status OPEN → tier logic." Compiling the two thoughts into one honest formula is easier when each part was built by a tool that got its own part right, rather than a single hand-written concatenation of both.

The builder is a page, not a service. Your condition rows, your thresholds, your preview values, the compiled formula — all of it lives in the tab's memory and none of it is transmitted anywhere. There is no account, no saved-work claim to evaluate, no formula history on a server. When you close the page, the rule you built is gone, which for grade scales and salary bands and approval thresholds is not merely convenient but correct: those numbers describe people's pay and performance, and the fewer places they exist, the fewer places there are to leak from.

It also works offline once the page is loaded, since the compiler is plain JavaScript already in the browser. The commuter building a grading scale on a train with no signal is a real person and a real use case, and they get the same tool as everyone at a desk.

The IF builder is one corner of a small formula shop. When the rule outgrows tiers and becomes a lookup, the XLOOKUP Formula Builder and VLOOKUP Formula Generator generate the retrieval side. When conditions turn into conditional aggregation — "sum only the rows where" — the SUMIF/COUNTIF Formula Builder is the right instrument. And for dissecting a formula someone else wrote, Excel Formula Explainer reads it back to you clause by clause.