Using IF and OR function in a formula

I am so close to getting this, but each time I tweak it, I get an UNPARSEABLE message. I am trying to get the Column Titled: Duration (In Hrs) to zero out if EITHER the Not Applicable or Optional rows are checked. The below formula is only making my column 0 out if BOTH the Not Applicable and Optional rows are checked. I'm stumped and I know the answer is right in front of me. Any tips are greatly appreciated!


=IF(OR([Not Applicable]@row = false, (Optional@row = false)), [Duration (In Hrs)]@row, 0)

Tags:

Best Answer

  • Toufong Vang
    Toufong Vang ✭✭✭✭✭
    Answer ✓

    I see...Sorry, I misread what you were trying to do. When the boxes are checked, their state is "1" or "true". So change the formula to...

    =IF( OR([Not Applicable]@row = true, Optional@row = true), 0, [Duration (in Hrs)]@row)

Answers

  • Toufong Vang
    Toufong Vang ✭✭✭✭✭

    Hi, @Sarah Clark

    Try...

    =IF( OR([Not Applicable]@row = false, (Optional@row = false)),0, [Duration (In Hrs)]@row)

    You had reversed switched the position of the value you wanted when your logic evaluates to "True". Below is the syntax (structure) for the IF() function.

    IF( logical_expression, value_if_true, [ value_if_false ])

    Cheers!

  • That's the closest I've got to it getting right! Now when I check one of those (Not Applicable or Optional) boxes my formula box (Hours Assigned) column is reflecting 0 like I want it to. If NEITHER of those are checked, though, I wanted the Duration (In Hrs) to reflect in Hours Assigned. It's showing 0 with neither the Not Applicable or Optional boxes checked, though. Is what I'm after even possible?

    If Not Applicable is checked, Hours Assigned = 0. If Optional is checked, Hours Assigned = 0. If neither of those boxes are checked then Duration (In Hrs) should = Hours Assigned.

    Like below, since neither Not Applicable and Optional are checked, my Hours Assigned are matching Duration (In Hrs). But as soon as either Not Applicable or Optional get checked, then Hours Assigned should 0 out.


  • @Toufong Vang - should have tagged you in the above comment. Whoops! :)

  • Toufong Vang
    Toufong Vang ✭✭✭✭✭
    Answer ✓

    I see...Sorry, I misread what you were trying to do. When the boxes are checked, their state is "1" or "true". So change the formula to...

    =IF( OR([Not Applicable]@row = true, Optional@row = true), 0, [Duration (in Hrs)]@row)

  • @Toufong Vang - you are my hero! I won't admit to how long I messed around with this formula trying to get it right!!! THANK YOU!

  • Toufong Vang
    Toufong Vang ✭✭✭✭✭

    We've all been there. Just glad to have been of help for you.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!