Converting a formula for automatic formatting criteria

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rominall - 22 Oct 2007 20:17 GMT

I have this formula that works in excel.
=IF(AND($K$2>(SUM($U$2*0.8)),$K$2<(SUM($U$2*1.2))),"true","false")

And when I try to get it as conditional formatting I get the "not enough or
too many parenthesis" error.

This is what I tried in onditional formatting.
=if(and($k$2<(sum($u$2*.8)),$k$2>(Sum($u$2*1.2)))).

What am I doing wrong or can I not us the and function in conditional
formatting?

BTW, this is the 3rd criteria so don't tell me to split it up (the first two
are between functions)

Reply to this Message

Elkar - 22 Oct 2007 20:34 GMT

Don't use the IF function. In Conditional Formatting, it is implied that we
are looking for a formula that evaluates to TRUE.

Try this:

=AND($K$2>$U$2*0.8,$K$2<$U$2*1.2)

Also, the SUM functions are redundant since you only have one argument
inside of them.

HTH,
Elkar

> I have this formula that works in excel.
> =IF(AND($K$2>(SUM($U$2*0.8)),$K$2<(SUM($U$2*1.2))),"true","false")
[quoted text clipped - 10 lines]
> BTW, this is the 3rd criteria so don't tell me to split it up (the first two
> are between functions)

Reply to this Message

JE McGimpsey - 22 Oct 2007 20:40 GMT

Well...

First, you don't need to use the SUM() function at all (that's usually
the sign of an unreformed Lotus 1-2-3 user)

Second, using the IF function requires you at least have a TRUE value
specified (but you don't need the IF() at all).

Third, noting that your condition can only return TRUE if K2 and U2 are
both negative, it can be rewritten:

=AND(K2 < U2 * 0.8, K2 > U2 * 1.2)

Reply to this Message