Is there a conditional format formula complexity limit?

[paulkinzelman]

I'm trying to conditionally format (Excel 2000) a cell and it's not working, any help or pointers would be appreciated.

My formula is AND(B10<l9,or(a9=a10,l9<b9,e9<b9,l9<e9))

I can put each comparison in a separate cell and they all work correctly.

I can even put that whole formula in a cell and it evaluates correctly (to true).

I can even conditionally format a cell with a trivial formula that merely points to the cell containing the above evaluation (to true) and it works.

But when I make the conditional format actually be this formula, it doesn't work, the color does not change as I specify.

I've noticed this once before with another similarly complex formula, but just punted.

Is there some maximum limit to nestings or length or something in conditional formatting?

Thanks!</l9,or(a9=a10,l9<b9,e9<b9,l9<e9))

 

[Special-K99]

The best way to resolve these type of problems is to post your spreadsheet.
Not that I doubt what you're saying but the formula looks fine so it must be a data problem.
Post your spreadsheet and let's see if we can fix this.

 

[T. Valko]

I'm trying to conditionally format (Excel 2000) a cell and it's not working, any help or pointers would be appreciated.

My formula is AND(B10<L9,OR(A9=A10,L9<B9,E9<B9,L9<E9))

I can put each comparison in a separate cell and they all work correctly.

I can even put that whole formula in a cell and it evaluates correctly (to true).

I can even conditionally format a cell with a trivial formula that merely points to the cell containing the above evaluation (to true) and it works.

But when I make the conditional format actually be this formula, it doesn't work, the color does not change as I specify.

I've noticed this once before with another similarly complex formula, but just punted.

Is there some maximum limit to nestings or length or something in conditional formatting?

Thanks!</L9,OR(A9=A10,L9<B9,E9<B9,L9<E9))[ QUOTE]

There's a limit as to the length of the formula that can be used. It must be less than 256 characters long. Your formula is well within that limit.

We'd need to know what's in all those cells to figure out what the problem might be.

 

[Fazza]

I guess you've got an "=" at the front of the formula?

And be sure the active cell is the right one for that specific formula - such as a cell in row 9 or whatever row the formula assumes.

 

[paulkinzelman]

Thanks for the hints! But I think the formula is right because I can execute the parts of it and even the entire thing *into* a cell with my example data and it works. It just doesn't work when in a conditional format window as though the formula is too complicated or something.

And when I type in the formula to the conditional format window, it puts a "=" at the start.

Except as noted, the cells are set to have the format of Time in the form 12:34.

B10=7:00
L9=7:30
A9=4 (just a number)
A10=5 (just a number)
B9=7:00
E9=21:30

Formula:
AND(B10<L9,OR(A9=A10,L9<B9,E9<B9,L9<E9))

 

[Marcelo Branco]

And when I type in the formula to the conditional format window, it puts a "=" at the start.

Yes it puts, but if you do not insert manually the equal sign (=) Excel also wraps the formula with double quotes and the formula becomes a string...
<l9,or(a9=a10,l9<b9,e9<b9,l9<e9))[ QUOTE]

M.
</l9,or(a9=a10,l9<b9,e9<b9,l9<e9))[>

 

[Fazza]

It works OK for me, Paul. Are you sure the activecell when the conditional format formula is created is correct (relative to the cell references in the formula)?

 

[Marcelo Branco]

It works OK for me, Paul. Are you sure the activecell when the conditional format formula is created is correct (relative to the cell references in the formula)?

Hi Fazza,

Worked ok for me too but inserting, like you said above,

=AND(B10 < L9,OR(A9=A10,L9 < B9,E9 < B9,L9 < E9))<b9,e9<b9,l9<e9))<l9,or(a9=a10,l9<b9,e9<b9,l9<e9))


M.</b9,e9<b9,l9<e9))<l9,or(a9=a10,l9<b9,e9<b9,l9<e9))

 

[paulkinzelman]

I didn't understand Fazza's question (relative to the cell references), but Marcelo Branco wins the prize of rescuing me from my stupidity.

That appears to be the problem, I didn't have an "=" sign in front of the formula so it thought the formula was a string. I thought it was just being helpful.

Thanks guys!

 

[T. Valko]

I didn't understand Fazza's question (relative to the cell references), but Marcelo Branco wins the prize of rescuing me from my stupidity.

That appears to be the problem, I didn't have an "=" sign in front of the formula so it thought the formula was a string. I thought it was just being helpful.

Thanks guys!

You have to be careful with that. For whatever reason, sometimes Excel likes to put quotes around your formula. Also... although MS made a lot of nice additions to the conditional formatting features they also ruined the user interface for conditional formatting in Excel 2007. It used to be so much easier in the earlier versions!

Thanks for the feedback!