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)

Answers

  • KPH
    KPH ✭✭✭✭✭✭
    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!