Nest IF statement

Sean_H

Hello!

I need some assistance with a nested IF statement. I am attempting to select a contract category based on expected amount of the contract. Everything seems to work properly except when letter values are included in the contract cost which I was hoping to return a value of "Other".

This is the formula I am using: =IF([Contract Amount]1 = "", "Blank", IF(FIND("Variable", [Contract Amount]1) > 0, "Variable", IF([Contract Amount]1 < 25000, "<25k", IF(AND([Contract Amount]1 >= 25000, [Contract Amount]1 < 50000), "25k-50k", IF(AND([Contract Amount]1 >= 50000, [Contract Amount]1 < 100000), "50k-100k", IF([Contract Amount]1 >= 100000, ">100k", "Other"))))))

And here is a link to a sheet https://app.smartsheet.com/b/publish?EQBCT=bb26dd85c00e4d1fb81087f5fd443d43

The two variables I know will be consistent, Blanks and Variables work out well. And when an actual amount is entered it properly assigns the category. But when something else is entered a value category is returned instead of the intended Other response.

Thanks for for any help!

Lee Joramo

The formula is assuming that the 'text' is a number. So we need to remove the non-numeric cells from the numeric range comparisons. We can do this by using the ISNUMBER() function.,

Try this (formatted for reading with comments):

=IF(

    [Contract Amount]1 = "",

    "Blank",

    IF(

        FIND("Variable", [Contract Amount]1) > 0,

        "Variable",

        IF(

            ISNUMBER([Contract Amount]1),  <--- make sure we have a number

            IF(

                [Contract Amount]1 < 25000,

                "<25k",

                IF(

                    AND(

                        [Contract Amount]1 >= 25000,

                        [Contract Amount]1 < 50000

                    ),

                    "25k-50k",

                    IF(

                        AND(

                            [Contract Amount]1 >= 50000,

                            [Contract Amount]1 < 100000

                        ),

                        "50k-100k",

                        ">100k"

                    )

                )

            ),

            "OTHER"  <--- ISNUMBER was false

        )

    )

)

 

And formatted for copy and paste:

=IF( [Contract Amount]1 = "", "Blank",  IF( FIND("Variable", [Contract Amount]1) > 0,  "Variable", IF( ISNUMBER([Contract Amount]1), IF( [Contract Amount]1 < 25000,  "<25k",  IF( AND( [Contract Amount]1 >= 25000,  [Contract Amount]1 < 50000 ),  "25k-50k",  IF( AND( [Contract Amount]1 >= 50000, [Contract Amount]1 < 100000 ), "50k-100k",  ">100k" ) ) ), "OTHER" )  ) )

I put this formula in your example sheet.

--

Lee Joramo

Data Integrations Developer - Mesa County Valley School Dist. 51

970-254-5104  x11556

Sean_H

Amazing! Thank you so much for the help.

Paul Newcome

You can also save yourself some characters by writing it in the opposite order to allow yourself to be able to remove all of the AND functions comparing to a high and low range.

 

=IF([Contract Amount]@row > 100000, ">100k", IF([Contract Amount]@row > 50000, ">50k", ...................................................)))))

.

You can basically use the previous IF statement being false as the high value of the next range.

.

You could also use the same theory by using a false value from the previous IF statement being the low value of the next range.

 

=IF([Contract Amount]@row <25000, "<25k", IF([Contract Amount]@row > 50000, "25k - 50k", ...................................................)))))

.

The fact that the first IF statement is false automatically means that the value is above the established number. That means having to specify it in an AND statement is actually a redundancy that opens up room for errors, typos, etc.

 

You actually used this theory when you didn't include in each of your AND statements the [Contract Amount]@row was NOT "Variable" and NOT "". Because they weren't true, you moved on to the next statement instead of repeating that they needed to be false.

.

Not to say you are wrong. Just providing another way of looking at things to help make things a little more efficient. I personally would write your original formula as

.

=IF([Contract Amount]@row= "", "Blank", IF(CONTAINS("Variable", [Contract Amount]@row), "Variable", IF([Contract Amount]@row< 25000, "<25k", IF([Contract Amount]@row< 50000), "25k-50k", IF([Contract Amount]@row< 100000), "50k-100k", IF([Contract Amount]@row>= 100000, ">100k", "Other"))))))

Sean_H

Thank you for the suggestion Paul. I have to work on another fiscal year so I will try it this way to reduce the length of the formula.