Forum Discussion

jamessavery1996's avatar
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...
excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    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's avatar
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's avatar
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's avatar
    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's avatar
      SergeiBaklan
      Diamond Contributor
      May 31, 2021

      jamessavery1996 

      Sorry, I didn't catch. Do you mean

      =IF(G6=1, "A", <your formula>)
      • jamessavery1996's avatar
        jamessavery1996
        Copper Contributor
        May 31, 2021
        Haha, that's what I did - but I forgot to add the additional 'IF' into the equation!

        I now also have another small formula I need to change, but this is a formula I've edited myself, not written from scratch:

        =if(not(or(B5="A",B5="B",B5="E",B5="A_R",B5="A_S")),if(D5="","",if(if(mod(D5-$C5,1)*24<4,5,0)=0,"",if(D5="","",if(mod(D5-$C5,1)*24<4,5,0)))),"")

        This needs to still run, but... if B5=D and K4 has a time in it, then this also results in a black space (if I'm at all making sense)

Resources