Knowledge base / Excel

IFS in Excel: threshold order, boundaries and defaults

I derive categories from a small score table, show how condition order changes results and separate a fallback from valid input.

Kajo RudzińskiPublished Updated 6 min read

A marker enters a side chute at the first matching gate on an ordered track.

IFS assigns a result using a sequence of condition-and-value pairs. I begin by writing down the category boundaries, because a syntactically correct formula can still assign the wrong category when conditions appear in an unsuitable order. Its result corresponds to the first TRUE condition, making order part of the rule rather than a cosmetic choice.

The official Polish name is WARUNKI, as the Microsoft Polish reference confirms. “JEŻELI.WARUNKI” is incorrect. The Polish page retains its historical address while using the correct name throughout the revised formulas. The examples below use synthetic data.

Availability and syntax

IFS is available in Microsoft 365 and Excel 2019, 2021 and 2024. Do not assume it exists in perpetual Excel 2016; check the recipient’s version before sharing a workbook. English examples here use commas between arguments, although regional settings can require semicolons. Function language and argument separators should both be considered when transferring a formula between environments.

=IFS(logical_test1,value_if_true1,[logical_test2,value_if_true2],...)

The chosen answer belongs to the first condition that evaluates to TRUE. Microsoft documents a maximum of 127 conditions, but I would not treat that limit as a design target. The rules still need to be understandable and maintainable. Dozens of hidden thresholds can be difficult to update even when the function accepts the formula without a complaint.

Classify a small set of scores

Assume valid numeric scores between 0 and 100. Enter Points in A1 and Category in B1, followed by these synthetic values. Column B shows the expected category under the rule below, rather than additional source data to include in the calculation.

Points Category
95 A
90 A
89 B
75 B
74 C
50 C
49 D
0 D

Enter the following in B2 and copy it down:

=IFS(A2>=90,"A",A2>=75,"B",A2>=50,"C",TRUE,"D")

For 95, the first comparison is true and selects A. For 89, the first is false and the second true, selecting B. For 74, the threshold at 50 selects C. For 49, all three threshold comparisons are false, so the final TRUE-and-D pair supplies the fallback. Each result can be established directly from the small table and the condition order.

The >= operators include the boundary itself. Thus 90 receives A, 75 receives B and 50 receives C. Replacing >=90 with >90 would send exactly 90 to the next category. I examine these boundary values separately instead of relying only on convenient examples in the middle of each interval. The boundary often contains the actual business decision the formula needs to implement.

Why these thresholds run downwards

When testing lower bounds with >=, start with the highest threshold. Consider this incorrectly ordered version:

=IFS(A2>=50,"C",A2>=75,"B",A2>=90,"A",TRUE,"D")

For 95, all three comparisons are true. The result is nevertheless C, because the first matching pair uses the threshold at 50. IFS does not select the largest matching threshold or reorder the conditions for you. If the rules have priorities beyond numeric size, the condition order must reflect those priorities explicitly.

The same issue arises with text categories and exceptions. A special case can be hidden by a more general condition placed earlier. Before constructing the formula, describe which inputs each rule should cover. Choose an example for every possible answer, then compare it with that written definition. This establishes the intended classification independently of the formula’s visual arrangement.

Fallback values and no match

=IFS(A2>=90,"A",A2>=75,"B",A2>=50,"C")

For the valid numeric input 49, none of these conditions is true, so this version returns #N/A. Adding TRUE,"D" supplies the missing category for lower scores. Microsoft’s IFS documentation describes this default-value pattern. It is an additional condition that always matches, rather than a dedicated else argument.

Putting TRUE first would select its answer for all valid inputs. Keep the fallback last. I would not wrap the whole formula in IFERROR merely to conceal an incomplete category definition. Fix the rules first. Broad error handling could also conceal an invalid reference or a problem in the data, as the separate IFERROR article explains.

Markers settle at their matching levels along an ordered set of thresholds.

Missing and invalid inputs need their own rule

The example assumes a genuine number from 0 to 100. An empty cell, text saying “missing”, a negative value, 120 or an upstream error violates that input agreement. A final TRUE condition does not validate the score. Separate valid records from those requiring investigation before assigning a category, using an explicit input-quality rule appropriate to the source.

A form can restrict entered values; an import can check types and allowed ranges before classification. Avoid treating an empty score as a genuine zero merely because that makes a completed table look convenient. Zero represents an actual score here, whereas an absent result needs a separate status. Agree that distinction with the owner of the assessment process before presenting the categories as meaningful outcomes.

Also avoid placing a risky calculation in a later pair on the assumption that it can never be evaluated. “The first TRUE condition selects the answer” describes the returned result; it is not a general execution guarantee for arbitrary nested calculations. All outcomes in this example are simple labels, keeping attention on category selection and boundaries rather than evaluation side effects.

IF or a separate rules table

The same classification can be written using nested IF:

=IF(A2>=90,"A",IF(A2>=75,"B",IF(A2>=50,"C","D")))

For valid inputs in the table, the labels are identical. IFS offers a flatter sequence of pairs that can be easier to read. That does not establish an automatic performance advantage. For two possible outcomes, ordinary IF is often sufficient. Choose the form that makes the rule clear to the person maintaining it.

If thresholds change frequently, consider storing them in a separate rules table and selecting a suitable lookup method. That exposes the rules without requiring someone to untangle a long formula. First establish ordering, non-overlap and coverage, however. Moving an ambiguous rule into a table does not resolve its underlying definition or make the classification inherently correct.

Exercise with boundary values

Derive the main formula’s results for 89, 90 and 91: B, A and A. Then raise the A threshold to 92 and inspect the same values again: all three receive B. Finally, remove the fallback and consider 49. Explain why there is no match and why adding a final condition resolves that problem without replacing input validation.

Key takeaways

What is the correct Polish name of IFS?

WARUNKI. JEŻELI.WARUNKI is incorrect; the historical Polish article address remains unchanged while the formulas use the official name.

Which answer does IFS select?

The value corresponding to the first TRUE condition. For lower-bound checks using >=, start with the highest threshold.

Why place TRUE at the end?

It supplies a final matching condition with a default answer. Without a true condition, IFS returns #N/A. A fallback does not validate the input.

AI was used in an automated way while working on this article. If you want to learn how to do it too, check out this project.

Kajo Rudziński

Newsletter KajoData

Let’s keep in touch.

I write about data analysis, learning and changing careers. My newsletter and the sign-up form are in Polish. If that works for you, I’d be glad to see you there.