Forum Discussion

jamessavery1996

Copper Contributor

May 07, 2021

Solved

Running an 'if' function if other criteria is met...

Hello Excellers! If have the following formula in my spreadsheet: =if(if(D5="", "", if(mod(D5-$C5,1)*24<4, 5, 0))=0, "", if(D5="", "", if(mod(D5-$C5,1)*24<4, 5, 0))) However, I'd like t...

Formulas and Functions

May 07, 2021

jamessavery1996

Perhaps something like this

=IF( NOT(OR(B5="C",B5="D")),
  IF(D5="",
    "",
    IF(
        IF(MOD(D5-$C5,1)*24<4,5, 0)=0,
        "",
        IF(D5="",
          "",
          IF(MOD(D5-$C5,1)*24<4, 5, 0)
        )
    )
  ),
"")

Diamond Contributor

May 07, 2021

jamessavery1996

And if it contains Z or something else?

jamessavery1996

Copper Contributor

May 07, 2021

It won't. There's another formula in 'B' that ensures they contain one of the following: A, B, C, D, E, A_R, A_S. So I need the above formula to only run/apply if the relevant 'B' contains either letter C or D.

SergeiBaklan
Diamond Contributor
May 07, 2021
jamessavery1996

Perhaps something like this

=IF( NOT(OR(B5="C",B5="D")), IF(D5="", "", IF( IF(MOD(D5-$C5,1)*24<4,5, 0)=0, "", IF(D5="", "", IF(MOD(D5-$C5,1)*24<4, 5, 0) ) ) ), "")
- jamessavery1996
  Copper Contributor
  May 29, 2021
  SergeiBaklan that was perfect; but I now need another edit which I also can't figure out...
  
  This is the formula: =IF(AND(D6="", K6="", H6="", N6=""), "A", IF(OR(B6="A", B6="A_S", B6="A_R"), "B", IF(B6="B", IF(D6=K6, IF(OR(AND(D5<>"", E6=1), AND(K5<>"", L6=1)), "C", "D"), IF(OR(AND(D6<>"", D5<>"", L5=""), AND(K6<>"", K5<>"", E5="")), "C", "D")), IF(B6="C", IF(OR(D6<>"", K6<>""), "E", "A"), IF(B6="D", IF(D6=K6, IF(OR(AND(D5<>"", E6=1, L5<>1), AND(K5<>"", L6=1, E5<>1)), "C", "D"), IF(OR(AND(D6<>"", D5<>"", L5<>1), AND(K6<>"", K5<>"", E5<>1)), "E", "A")), IF(B6="E", IF(AND(H6<>"", O6=""), "A_R", IF(AND(N6<>"", I6=""), "A_S", "A"))))))))
  
  What I need, is for the column to result in 'A' if G6=1, overriding the other formula there. So the above runs as normal but if G6 is 1, then 'A' is the result.
  - SergeiBaklan
    Diamond Contributor
    May 31, 2021
    jamessavery1996
    
    Sorry, I didn't catch. Do you mean
    
    =IF(G6=1, "A", <your formula>)

Resources