Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Help with IF/AND formula
Hi--I've been reviewing sample Smartsheet formulas from discussions past, but can't quite get my (relatively simple) formula to work.
I'm trying to return a checkbox for a given row (let's say 1) if "Rehab?" column is "Yes" AND "Xtra Cost (cog, rehab, tx, etc.) is blank.
I've tried several different formulas, but most recently:
=IF(AND([Rehab?]1 = Yes, [Xtra Cost (cog, rehab, tx, etc.)]1 = 0), 1, 0)
I'm not really sure how to indicate blank (I tried = 0) for starters...
Thanks for any help.
P.S. For future reference, would be good to know how to indicate not blank, i.e. if anything is entered in a given field.
Comments
-
José Joaquín
Mil gracias. The devil's always in the details. Saludos!
Josh
-
Hi Joshua,
the formula is ok, just remenber to surround text strings with quotation marks.
=IF(AND([Rehab?]1 = "Yes", [Xtra Cost (cog, rehab, tx, etc.)]1 = 0), 1, 0)
I like use "" to indicate blank, I feel it more clear, but 0 work too.
And there's a formula to check if a cell is blank ISBLANK(@cell)
=IF(AND(Rehab?1 = "Yes", ISBLANK([Xtra Cost (cog, rehab, tx, etc.)]1)), 1, 0)
(This formula doesn't work if the Xtra Cost is 0, just when Xtra Cost is blank)
Regards, José Joaquín.
-
=IF(AND(Eval Payment Status]1 = "Appointment Pending (pay plan)", [Initial Pay Plan Amount Due]1 = 0), 1, 0)
I'm losing my mind. Why would this not be working. Exact same logic...!
-
Hi Joshua,
You miss an open square bracket before Eval Payment Status.
=IF(AND([Eval Payment Status]1 = "Appointment Pending (pay plan)", [Initial Pay Plan Amount Due]1 = 0), 1, 0)
Regards, José Joaquín.
-
Joshua
I see you want to use the formula in a checkbox. First make sure that you select "Checkbox" as the Column Type. The following formula, it true, should then mark the Checkbox.
=IF(AND([Rehab?]1 ="Yes", [Xtra Cost (cog, rehab, tx, etc.)]1 = 0), 1, 0))
Hope this helps.
-
Jose
Please be aware "" = NULL which is not the same as using a zero to indicate a blank. NULL is litterally "no value", whereas a 0 is actually still a valid number value value for a text/number data type.
Best example i can give you is if you have a "true"/"false" trigger - those two values are also represented as 0 & 1. If you return a 0 instead of a NULL as a part of a non-trigger event, you would actually send a "false" trigger instead of "no trigger"
-
Good point, 15UZU! And thanks Gerhard!
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives