Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF OR ???
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<=T1 9*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... |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF OR ???
"j5b9721" wrote:
=IF(OR(V17<=T19*0.01+T19,W17<=T19*0.01+T19,X17<=T1 9*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,"Lo g","") 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 ----- "j5b9721" wrote in message ... 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<=T1 9*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... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|