Failure Using Logical Operators in Cond. Fmting Formulas

[Cyberdude]

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??

[Cyberdude]

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]

Try

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

[TonyJollans]

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]

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]

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]

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]

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]

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]

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]

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]

It would seem this isn't the first time/location this has arisen .. http://www.mrexcel.com/board2/viewto...t=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/viewto...t=indirect+row
http://www.mrexcel.com/board2/viewto...t=indirect+row
http://www.mrexcel.com/board2/viewto...+dereferencing **
http://www.mrexcel.com/board2/viewto...=dereferencing
http://www.mrexcel.com/board2/viewto...=dereferencing

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

[TonyJollans]

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]

Nope, not by my watch anyway. Did you try it Tony?

[TonyJollans]

Grovel, grovel, I'm sorry - you are correct - any non-zero number is taken as True.

, 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.

[Zack Barresse]

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.

[Bob Phillips]

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]

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]

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* !!

[Bob Phillips]

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* !!

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