May 07 2021 09:27 AM
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
May 07 2021 09:32 AM
And if it contains Z or something else?
May 07 2021 09:34 AM
May 07 2021 09:58 AM
SolutionPerhaps 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)
)
)
),
"")
May 07 2021 10:15 AM
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)),
"")
May 29 2021 02:20 PM
@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.
May 31 2021 02:32 AM
May 31 2021 03:08 AM
May 31 2021 04:55 AM
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))))
,"")
May 31 2021 07:35 AM
May 31 2021 10:43 AM
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?
May 07 2021 09:58 AM
SolutionPerhaps 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)
)
)
),
"")