# I need help with creating a formula that is assigning a value to text but excludes blank and "N/A"

Options
✭✭✭✭

I have a dropdown column that is restricted to dropdown options only. I have a text/number helper column that I am placing the formula in. I want to create a formula that assigns a value if the dropdown column does not have a blank or N/A. So far I have the following formula but it is still placing a value if the column is blank or has an 'N/A',

=IF(OR(NOT(ISBLANK([Tuesday, 1/9/24 Virtual Pre-Session 1 8:30 -10 am]@row)), NOT(CONTAINS("N/A", [Tuesday, 1/9/24 Virtual Pre-Session 1 8:30 -10 am]@row))), 1.5)

• ✭✭✭✭✭✭
Options

Your IF needs some logic and then a value if true and a value if false:

=IF(logic,value if true, value if false)

I find it is easier to work with positives than double negatives so would use these as the logic statements:

ISBLANK([Tuesday, 1/9/24 Virtual Pre-Session 1 8:30 -10 am]@row)

[Tuesday, 1/9/24 Virtual Pre-Session 1 8:30 -10 am]@row = "N/A"

combined together in an OR

(OR(ISBLANK([Tuesday, 1/9/24 Virtual Pre-Session 1 8:30 -10 am]@row), [Tuesday, 1/9/24 Virtual Pre-Session 1 8:30 -10 am]@row = "N/A")

And then popped into the IF statement to return nothing if true, and 1.5 if false.

=IF(OR(ISBLANK([Tuesday, 1/9/24 Virtual Pre-Session 1 8:30 -10 am]@row), [Tuesday, 1/9/24 Virtual Pre-Session 1 8:30 -10 am]@row = "N/A"), "", 1.5)

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!