Problem with Nested IF/OR

[andrewb90]

Hello,

I am using this formula:

=IF(H35=0,"-",(IF(OR($M$23>M35,$M$26>M35,$M$29>M35,$M$32>M35,$M$35>M35,$M$38>M35,$M$41>M35,$M$44>M35,$M$47>M35,$M$50>M35,$M$53>M35,$M$56>M35,$M$59>M35,$M$62>M35,$M$65>M35,$M$68>M35,$M$71>M35,$M$74>M35,$M$77>M35,$M$80>M35,$M$83>M35,$M$86>M35,$M$89>M35,$M$92>M35,$M$95>M35,$M$98>M35,$M$101>M35,$M$104>M35,$M$107>M35,$M$110>M35),"YES","NO")))

M35 has "-" as the default value. Now once H35 has a time typed into it, this cell (D35) evaluates to "NO". Once I type in a time value into M35 the value changes to "YES". However it is supposed to not change until another cell in the M column (every third cell as you can see) has a time entered that is greater than the time in M35.

Did I mess something up in this formula?

 

[vogel997]

=if(h35=0,"-",if(or($m$23>m35,$m$26>m35,$m$29>m35,$m$32>m35,$m$35>m35,$m$38>m35,$m$41>m35,$m$44>m35,$m$47>m35,$m$50>m35,$m$53>m35,$m$56>m35,$m$59>m35,$m$62>m35,$m$65>m35,$m$68>m35,$m$71>m35,$m$74>m35,$m$77>m35,$m$80>m35,$m$83>m35,$m$86>m35,$m$89>m35,$m$92>m35,$m$95>m35,$m$98>m35,$m$101>m35,$m$104>m35,$m$107>m35,$m$110>m35),"yes","no"))

 

[andrewb90]

I tried that, and got the same result. What's different with this formula?

 

[ukmikeb]

Andrew

Try :-

=IF(H35=0,"-",IF(SUMPRODUCT(--($M$23:$M$110>M35),--(MOD(ROW($M$23:$M$110)-23,3)=0)),"YES","NO"))

hth

 

[andrewb90]

I seem to be getting the same result. As soon as I type a time into M35 the value changes to YES, but it should wait until another value exceeds it before changing to YES.

 

[Aladin Akyurek]

Perhaps you should describe your problem in words instead of a formula.

 

[Jon von der Heyden]

duplicate thread: http://www.mrexcel.com/forum/excel-questions/800624-if-help.html#post3914402

All follow-ups and clarifications should be posted back to the original thread per the forum rules.