Nest IF statement
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!
Comments
-
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 -
Amazing! Thank you so much for the help.
-
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"))))))
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!