Help with IF/AND formula

Joshua Rose

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.

 

Joshua Rose

José Joaquín

 

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

 

Josh

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.

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

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.

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.

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"

Joshua Rose

Good point, 15UZU! And thanks Gerhard!