Nest IF statement

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

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"))))))