# Nested IF() Formulas with Checkboxes

#1

Here is what I have: 4 columns labeld as follows.

1. Total Units:
2. Tier: (Single Select with options 1-5)
3. OOM? (Checkbox)
4. Total Pay:

I need a formula to configure total units1.95 for tier 1 and OOM? not checked and total units2.70 for tier 1 and OOM? checked.

And I need this to happen with tiers 1-5.
Tier 1: 1.95, 2.70 with OOM? checked
Tier 2: 2.15, 2.90 with OOM? checked
Tier 3: 2.35, 3.10 with OOM? checked
Tier 4: 2.55, 3.30 with OOM? checked
Tier 5: 2.75, 3.50 with OOM? checked

#2

Youâre actually pretty close to the answer here:

And AndrĂ©âs solution to a problem similar to yours will work for you as well:

Just chain the IF functions together with `+`, like so (Iâve done the first two, but you can extend this to as many tiers as you need):
`IF({Tier:}=1,IF({OOM?}=1, {Total Units:}*2.70, {Total Units:}*1.95))+ IF({Tier:}=2,IF({OOM?}=1, {Total Units:}*2.90, {Total Units:}*2.15))+...`

#3

Youâre awesome. Thanks. Didnât even know you could chain together with a + sign. Thanks so much.

#4

Not to complicate things needlessly, but thanks to the nice, orderly price progression, you could bypass the whole nested `IF()` structure with this formula:

``````{Total Units:}*(IF({OOM:},2.7,1.95)+(({Tier:}-1)*0.2))
``````

That starts with a base price of either 1.95 (without OOM) or 2.70 (with); adds 0.0 for Tier 1, 0.2 for Tier 2, up to 0.8 for Tier 5; and finally multiplies the final price by the number of units.

Downside: Future price changes may make it difficult to support down the road.
Downside to nested `IF()`s: Also potentially difficult to support, as price changes require multiple values within the formula to be modified.

Also, keep in mind that chained `IF()` statements â that is, ones linked with plus signs â actually sum together each individual statement. In your case everything still works fine, since only one Tier level can be valid at a time, and there is no âelseâ branch for the statements @Andy_Lin1 created. (Heâs actually chaining together a series of nested `IF()` statements, as so:

``````IF(
{Tier:}=1,
IF(
{OOM?}=1,
{Total Units:}*2.70,
{Total Units:}*1.95
)
)+
IF(
{Tier:}=2,
...
``````

and so on. In the second `IF()` statement above, there is an implied `ELSE` before `{Total Units:}*1.95`. Since the first statement lacks a comparable branch, if `{Tier:}` is not equal to `1` the statement evaluates to `0` (zero), and the formula moves along to test for `{Tier:}=2`.

If there is a chance of having two tests both prove true, you might want to use nested `IF()` statements. With nested statements, once a statement has evaluated to `'TRUE'`, all subsequent (that is, more deeply nested) `IF()`s are ignored. Your formula could be constructed several ways; one possible implementation would be:

``````IF(
{OOM?},
IF(
{Tier:}=1,
2.70,
IF(
{Tier:}=2,
2.90,
IF(
{Tier:}=3,
3.10,
IF(
{Tier:}=4,
3.30,
IF(
{Tier:}=5,
3.50
)
)
)
)
)
IF(
{Tier:}=1,
1.95,
IF(
{Tier:}=2,
2.15,
IF(
{Tier:}=3,
2.35,
IF(
{Tier:}=4,
2.55,
IF(
{Tier:}=5,
2.75
)
)
)
)
)
)*
{Total Units:}
``````

Again, though, these alternatives arenât better or more correct than those suggested earlier; they are simply different. The one to go with is whichever one seems to make the most sense to you â whichever seems like the logical solution. If you choose the one most attuned to your personal approach to problem solving, youâll have a better chance of figuring out what the heck it was you were trying to do six or twelve months from now when youâre called upon to support or enhance the code.