PDA

View Full Version : [SOLVED] Failure Using Logical Operators in Cond. Fmting Formulas



Cyberdude
05-15-2005, 02:45 PM
Every once in awhile (today being one of them) I'll write what appears to be an error-free formula for conditional formatting, and it just doesn't work. No error message.
The most common factor I have isolated is the use of the AND and OR operators. Today's failure is:

=AND(INDIRECT("C"&ROW()-1<>"",INDIRECT("C"&ROW()+1="")
If I copy the formula to a cell and execute it, it yields TRUE, as it should.
If I put the following formula:
=INDIRECT("C"&ROW()-1<>"" in a different condition for the same cell it works correctly.
If I then add the AND (or an OR) to get:

=AND(INDIRECT("C"&ROW()-1<>"")
it stops working.
I have used the boolean operators in other workbooks successfully, but in others they don't work. Today's attempt is in a new workbook with one worksheet. As I said, this happens to me every once in awhile. In one such instance, I transferred the formula to another workbook, and it worked. Any ideas?? :banghead:

Cyberdude
05-15-2005, 03:18 PM
Update to the preceding post:
I tried removing the ROW() and INDIRECT functions and using direct references:

=AND(C11<>"",C9="")
and it works. Now I'm wondering if it's the ROW() function that's causing the problem. Or maybe the INDIRECT() function. It doesn't alter the fact that maybe there's a bug in the conditional formating formula handling in some way.

Emily
05-16-2005, 02:12 AM
Try

=(INDIRECT("C"&ROW()-1)<>"")*(INDIRECT("C"&ROW()+1)="")

TonyJollans
05-16-2005, 02:50 AM
I'd vote for the INDIRECT() and ROW() combination being the culprit. Although it can be very useful I have seen cases where it simply doesn't work as advertised (especially in array formulae) and have never found a proper explanation.

Bob Phillips
05-16-2005, 03:10 AM
I'd vote for the INDIRECT() and ROW() combination being the culprit. Although it can be very useful I have seen cases where it simply doesn't work as advertised (especially in array formulae) and have never found a proper explanation.
I don't think so. If you try each individually, they work, It is the combination that doesn't.

TonyJollans
05-16-2005, 04:30 AM
I don't think so. If you try each individually, they work, It is the combination that doesn't.I thought that was what I said ..



I'd vote for the INDIRECT() and ROW() combination being the culprit.

Bob Phillips
05-16-2005, 04:50 AM
I'd vote for the INDIRECT() and ROW() combination being the culprit

And I said I don't I don't think so

Going back to the OP


=INDIRECT("C"&ROW()-1)<>""
works


=INDIRECT("C"&ROW())+1=""
works

but

=AND(INDIRECT("C"&ROW()-1)<>"",INDIRECT("C"&ROW())+1="")
does not work,

so it is not simply a INDIRECT ROW problem

TonyJollans
05-16-2005, 05:16 AM
Sorry, I misunderstood when you said combination.

However, I stand by what I said.

If you take the formula which doesn't work and hard code the row number in place of ROW(), for example in some column on row 9, put this formula in CF:


=AND(INDIRECT("C"&9-1)<>"",INDIRECT("C"&9+1)="")

then it works.

If you take the original formula and put it in a cell, as per the OP, then it works but if you array-enter it, it doesn't.

Bob Phillips
05-16-2005, 05:38 AM
Sorry, I misunderstood when you said combination.

No probs.


However, I stand by what I said.

If you take the formula which doesn't work and hard code the row number in place of ROW(), for example in some column on row 9, put this formula in CF:


=AND(INDIRECT("C"&9-1)<>"",INDIRECT("C"&9+1)="")

then it works.

If you take the original formula and put it in a cell, as per the OP, then it works but if you array-enter it, it doesn't.

I don't think that that is proof that it is the INDIRECT ROW is the problem (anymore than mine is a proof that it is not :)), there are just a number of factors involved, and it must be something to do with the way that the Excel parser is working (don't forget that


=(INDIRECT("C"&ROW()-1)<>"")*(INDIRECT("C"&ROW())+1="")

works okay

TonyJollans
05-16-2005, 06:07 AM
Certainly not proof :)

It was just that I have found inexplicable problems with the INDIRECT(), ROW() combination before - never stand-alone, always in conjunction with other functions and it's never been something I could pin down.

Cyberdude
05-16-2005, 12:32 PM
Emily, your suggestion to arrange it as a logical product DOES work. Nice work-around, but it doesn't alter the fact that Conditional Formatting would seem to be buggy when it comes to certain more complicated formulas. I did some more testing, and it's hard to pin down just what the culprit is. I tried it on 2 other workbooks, and it didn't work there either. Say, Emily, what do you have in your bag of tricks if I wanted to use OR instead of AND? I suppose I could add some NOT operators and using boolean rules convert it into another product. Interesting problem.

Zack Barresse
05-16-2005, 02:44 PM
It would seem this isn't the first time/location this has arisen .. http://www.mrexcel.com/board2/viewtopic.php?t=136829&highlight=indirect+row

In CF, try to stay away from logical operation functions, stick to their mathematical equivalents ..

AND = *
OR = +

So this formula ..


=AND(A1="",B1="")

.. would be the same as ..


=(A1="")*(B1="")

If you were to use an OR statement with the same function/CF it would be ..


=(A1="")+(B1="")


Some reading edification for you (Aladin's postings):
http://www.mrexcel.com/board2/viewtopic.php?t=130473&highlight=indirect+row
http://www.mrexcel.com/board2/viewtopic.php?t=109550&highlight=indirect+row
http://www.mrexcel.com/board2/viewtopic.php?t=8015&highlight=indirect+dereferencing **
http://www.mrexcel.com/board2/viewtopic.php?t=124434&highlight=dereferencing
http://www.mrexcel.com/board2/viewtopic.php?t=52214&highlight=dereferencing

** Probably the best description of INDIRECT you'll find on the web.

TonyJollans
05-16-2005, 04:30 PM
In CF, try to stay away from logical operation functions, stick to their mathematical equivalents ..

AND = *
OR = +

So this formula ..


=AND(A1="",B1="")

.. would be the same as ..


=(A1="")*(B1="")

If you were to use an OR statement with the same function/CF it would be ..


=(A1="")+(B1="")

Not quite, Zack.

To return a TRUE/FALSE result, OR needs to be translated as


=((A1="")+(B1=""))>0

Zack Barresse
05-17-2005, 08:34 AM
Nope, not by my watch anyway. Did you try it Tony? ;)

TonyJollans
05-17-2005, 09:59 AM
Grovel, grovel, I'm sorry - you are correct - any non-zero number is taken as True.

:banghead:, I did try it, b..u..t ...

only on the sheet (not in CF) and I was trying something else at the same time and had calculation set to manual and so I took what I saw as confirmation of what I expected without giving it much thought.:doh:

Zack Barresse
05-17-2005, 01:46 PM
I believe you Tony - I've done worse, MUCH worse. LOL!

By all means it's still confusing though. We're actually abusing Excel's (native?) coercing abilities. I know this is the source of much debate - especially at the NewsGroups. Because when you look at it, it evaluates to something like this ..


=(A1="")+(B1="")

=(TRUE)+(FALSE)

=TRUE+FALSE

=1+0

=1

Where the real *magic* happens is where it converts the boolean (True/False) to a binary (1/0) equivalent. Then a little more *magic* is after the conversion, it will represent a CF (needed) boolean-type return! Now how is that for backward-backward logic?!?!

Native or not, I exploit it. :D

Bob Phillips
05-17-2005, 02:08 PM
IBy all means it's still confusing though. We're actually abusing Excel's (native?) coercing abilities. I know this is the source of much debate - especially at the NewsGroups.

Not at all, any mathematical operator wil coerce a TRUE or FALSE to a numerical equivalent. So

=TRUE+FALSE

gets coerced by the +. And of course, all of these are logically equivalent

--TRUE
1*TRUE
TRUE*1
0+TRUE
TRUE+0
N(TRUE)

And of course the conversion back is no magic, as Excel (as against VBA) knows TRUE and 1 as the same thing, and o and FALSE as the same thing (note how you can have 0 or FALSE as arguments such as in VLOOKUP).

TonyJollans
05-17-2005, 03:03 PM
Yes, it is magic!

The arithmetic operator coerces the boolean to 'binary' - so far so good.

The (implicit) check for True then coerces the final numeric result into either False or Not False - I find this less intuitive but, hey, it works.

Zack Barresse
05-18-2005, 09:14 AM
We're in agreeance, Bob, with everything; but I have a reservation with what you said, "as Excel (as against VBA)." This means to me that it is not a native (predisposed of) feature. It is a byproduct of design. (Which we exploit.)

Let's just not get into an argument over -- compared to 0+ or 1* !! :D

Bob Phillips
05-18-2005, 10:20 AM
We're in agreeance, Bob, with everything; but I have a reservation with what you said, "as Excel (as against VBA)." This means to me that it is not a native (predisposed of) feature. It is a byproduct of design. (Which we exploit.)

No, although I didn't word it especially well, I don't think that was the point I was making. You can exploit a numerical value for True in both Excel and in VBA, in Excel the numerical equivalent of True is 1, but in VBA the numerical equivalent of True is -1. To demonstrate,

In Excel, in a cell type =N(TRUE), you get 1

In VBA, in the immediate window, type ?CLng(TRUE), you get -1,

whereas the numerical equivalent of FALSE is 0 in both.



Let's just not get into an argument over -- compared to 0+ or 1* !! :D

I didn't express a preference for any of the forms, although I do have one!:)

Zack Barresse
05-18-2005, 12:10 PM
No, although I didn't word it especially well, I don't think that was the point I was making. You can exploit a numerical value for True in both Excel and in VBA, in Excel the numerical equivalent of True is 1, but in VBA the numerical equivalent of True is -1. To demonstrate,

In Excel, in a cell type =N(TRUE), you get 1

In VBA, in the immediate window, type ?CLng(TRUE), you get -1,

whereas the numerical equivalent of FALSE is 0 in both.
Right, but the question here is why?!?! Can you find this in the Help files? No. I haven't seen any Microsoft documentation on this behavior. It has been exploited tremendously in the Newsgroups and had much conversation by many experts and MVP's (which include the likes of Aladin Akyurek, Harlan Grove, Tushar Mehta, the list goes on), to which you have various findings.

I think I'll leave the rest up to those experts and MVP's. ;)


I didn't express a preference for any of the forms, although I do have one!:)
I think we all do! :D