Using IF and OR function in a formula

06/30/21
Accepted

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, ([email protected] = false)), [Duration (In Hrs)]@row, 0)

Tags:

Best Answer

  • Toufong VangToufong Vang ✭✭✭
    Accepted 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, [email protected] = true), 0, [Duration (in Hrs)]@row)

Answers

  • Hi, @Sarah Clark

    Try...

    =IF( OR([Not Applicable]@row = false, ([email protected] = 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 VangToufong Vang ✭✭✭
    Accepted 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, [email protected] = 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!

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

Sign In or Register to comment.