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

Joshua Rose
Joshua Rose ✭
edited 12/09/19 in Archived 2017 Posts

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.

 

Tags:
· Share on FacebookShare on Twitter

Comments

  • Joshua Rose
    Joshua Rose ✭
    edited 02/15/17

    José Joaquín

     

    Mil gracias. The devil's always in the details. Saludos!

     

    Josh

    · Share on FacebookShare on Twitter
  • Jose Joaquin Pastor
    Jose Joaquin Pastor ✭

    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.

    · Share on FacebookShare on Twitter
  • Joshua Rose
    Joshua Rose ✭

    =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...! Surprised

    · Share on FacebookShare on Twitter
  • Jose Joaquin Pastor
    Jose Joaquin Pastor ✭

    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.

    · Share on FacebookShare on Twitter
  • Gerhard van Noordwyk
    Gerhard van Noordwyk ✭

    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.

    · Share on FacebookShare on Twitter
  • 15UZU
    15UZU ✭✭

    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"

    · Share on FacebookShare on Twitter
  • Joshua Rose
    Joshua Rose ✭

    Good point, 15UZU! And thanks Gerhard!

    · Share on FacebookShare on Twitter
This discussion has been closed.

Categories