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

[jamessavery1996]

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 this to run/only apply if other cells contain specific values. For example, if this formula is in P5, I'd like to to only run/apply if B5 contains the letter 'C' OR the letter 'D'. If it contains 'A', 'B', 'E', 'A_S' or 'A_R', it needs to not run/stay blank.

Anyone can help me? @excel

[#2]

@jamessavery1996 

And if it contains Z or something else?

[#3]

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.

[#4 · Accepted solution]

@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)
        )
    )
  ),
"")

[#5]

@jamessavery1996 

Perhaps, this formula in P5 fulfills your requirements: 

=IF(SUM(COUNTIF(B5,{"*c*","*d*"})),
IF(D5="","",
IF(MOD(D5-C5,1)*24<4,5,0)),
"")

[#6]

@Sergei Baklan 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. 

[#7]

@jamessavery1996 

Sorry, I didn't catch. Do you mean

=IF(G6=1, "A", <your formula>)

[#8]

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)

[#9]

@jamessavery1996 

IF B5 is "D" it depends on what is in D5

=
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))))
,"")

[#10]

D5 would be empty in this case.
As if D5 contained a time, then a score would appear.

[#11]

@jamessavery1996 

That's better to discuss with sample since not clear which data do you use and what is expected.

If take this part 

=IF(MOD(D5-$C5,1)*24<4,5,0)

with D5 empty it returns 5 for the time after 20:00 (4 hours before midnight), for the time after  12am and before 8pm it returns zero. Is that as expected?