Skip to main content

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


Can someone please help me figure this out?

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))+...

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))+...


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

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


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. :winking_face:

Reply


Terms & Conditions