IF OR ???

[j5b9721]

I wrote this is in long form to identify numbers below 11.19 in a daily log &
trigger Conditional Formating daily. But it only works when ALL cells are
filled.
v17 is Mon... z17 is Fri
T19 is 11.08 or empty trigger is 11.19 - works only when ALL cells are
filled
T20 is 8.15 trigger is 8.23 - works only when ALL cells are filled
I need this to work each entry day...

=IF(OR(V17<=T19*0.01+T19,W17<=T19*0.01+T19,X17<=T19*0.01+T19,Y17<=T19*0.01+T19,Z17<=T19*0.01+T19,V17<=T20*0.01+T20,W17<=T20*0.01+T20,X17<=T20*0.01+T20,Y17<=T20*0.01+T20,Z17<=T20*0.01+T20),"Log","")
And when I added Conditional Formating it dont work I wrote this
"Conditional Formating"
~ Cond. 1 ~"cell value is"~ equal to ~ "Log"
When its coppied into a empty data cell area the "LOG" is shown answers are
all "TRUE".
Can this be made shorter and to not accept an empty cell?


Thanks for looking...

 

[JoeU2004]

=IF(OR(V17<=T19*0.01+T19,W17<=T19*0.01+T19,X17<=T19*0.01+T19,
Y17<=T19*0.01+T19,Z17<=T19*0.01+T19,V17<=T20*0.01+T20,
W17<=T20*0.01+T20,X17<=T20*0.01+T20,Y17<=T20*0.01+T20,
Z17<=T20*0.01+T20),"Log","")
[....]
Can this be made shorter and to not accept an empty cell?

Forgive me if I misunderstand what you wrote, but perhaps the following
array formula (commit with ctrl-shift-Enter, not Enter) works for you:

=IF(OR((T19<>"")*(V17:Z17<>"")*(V17:Z17<=1.01*T19),
(T20<>"")*(V17:Z17<>"")*(V17:Z17<=1.01*T20)),"Log","")

Alternatively, the following normal formula (commit with Enter):

=IF(SUMPRODUCT((T19<>"")*(V17:Z17<>"")*(V17:Z17<=1.01*T19) +
(T20<>"")*(V17:Z17<>"")*(V17:Z17<=1.01*T20))>0,"Log","")

Caveat: This works if T19 or T20 might be truly empty (no formula). But if
by "empty", you mean that it appears blank due to a formula that might
results in "", change 1.01*T19 to 1.01*N(T19) and similarly for 1.01*T20.
The formula works fine as-is even if any of V17:Z17 might appear blank due
to a formula that results in "".


----- original message -----