asked on

Conditional Formatting with Excel - Part 6

Hi Experts,

Hopefully, I'll be done with conditional formatting after this request.

I've trying to conditionally format cells A2 through to A200 to highlight when a certain condition occurs(please see attached). Unfortunately, there is an error in my formulas.

I've tried both of the following:

=AND(F2<C2,J2<G2,G2<C2,J2<F2,G2>F2,N2<K2,K2<G2,N2<J2,K2>J2,R2>O2,(R2-O2>(P2-R2),(O2-Q2)>(R2-O2)*1.5),V2>S2,V2>R2,S2>O2,S2>K2)

=AND(F2<C2),(J2<G2,G2<C2,J2<F2,G2>F2),(N2<K2,K2<G2,N2<J2,K2>J2),(R2>O2,(R2-O2)>(P2-R2),(O2-Q2)>(R2-O2)*1.5),(V2>S2,V2>R2,S2>O2,S2>K2)

But I keep on getting error message with my formula.

I wonder if an expert could take a look and let me know where I might be going wrong

Cheers

Carlotn
Hammerv2.xlsm

StephenJR

Do these do the right thing? You had some missing (as well as superfluous) brackets.

=AND(F2<C2,J2<G2,G2<C2,J2<F2,G2>F2,N2<K2,K2<G2,N2<J2,K2>J2,R2>O2,(R2-O2)>(P2-R2),((O2-Q2)>(R2-O2)*1.5),V2>S2,V2>R2,S2>O2,S2>K2)

=AND(F2<C2,J2<G2,G2<C2,J2<F2,G2>F2,N2<K2,K2<G2,N2<J2,K2>J2,R2>O2,(R2-O2)>(P2-R2),((O2-Q2)>(R2-O2)*1.5),V2>S2,V2>R2,S2>O2,S2>K2)

cpatte7372

ASKER

Stephen,

Thanks for responding, I'm going to check it now...

cpatte7372

ASKER

Stephen,

Unfortunately it doesn't work.

I appreciate that my formula is probably incorrect. However, what I'm trying to do with this formula is calculate each bracketed area seperately. Does that make sense?

=AND(F2<C2),(J2<G2,G2<C2,J2<F2,G2>F2),(N2<K2,K2<G2,N2<J2,K2>J2),((R2>O2,(R2-O2)>(P2-R2),(O2-Q2)>(R2-O2)*1.5)),(V2>S2,V2>R2,S2>O2,S2>K2)

Cheers

StephenJR

I don't that brackets will make any difference with AND. I think

AND(1>2,(2>3,3>4))

is the same as

AND(1>2,2>3,3>4)

because as soon as single statement is False the whole statement will evaluate to False.

Perhaps you need OR? Can you explain what you are trying to do?

barry houdini

What do you mean "calculate each bracketed area separately"?

Within an AND function every condition needs to be TRUE for the whole thing to be TRUE, so if you have

=AND(A1>B1,D1>E1,X1>Y1)

that means that the result is only TRUE if each of the 3 conditions is TRUE

regards, barry

cpatte7372

ASKER

OK,

Give me second I will show what your formula produces....

cpatte7372

ASKER

Ok,

One of the conditions is that the value in cell J should be lower than the cell value in cell G. However if you look at cell J151, its higher than cell G151, yet I get a positive condition a highlighted in cell A151.

Hammerv3.xlsm

StephenJR

I think the CF is coming from your other condition.

StephenJR

I'm guessing that may be a mistake as it only seems to apply to row 151 alone.

cpatte7372

ASKER

Stephen,

I think you might be right. I need to run the conditional on different numbers. I'm going to check it out on different data now.

In meantime, can you see any reason why this formula doesn't work?

=AND(H2<D2,L2<H2,R2>O2,(R2>O2,(R2-O2)>(P2-R2),(O2-Q2)>(R2-O2)*1.5)

Cheers

StephenJR

Not sure, what you mean by not work? Not produce the expected result or produce an error?

As we were saying above, this could be simplified to:

=AND(H2<D2,L2<H2,R2>O2,(R2-O2)>(P2-R2),(O2-Q2)>(R2-O2)*1.5)

cpatte7372

ASKER

Stephen

I meant to say can you see any reason why the following formula won't work:

=AND(H2<D2,L2<H2,(R2>O2,(R2-O2)>(P2-R2),(O2-Q2)>(R2-O2)*1.5)

Cheers

ASKER CERTIFIED SOLUTION

StephenJR

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cpatte7372

ASKER

Stephen,

That worked. Cheers mate.

Tracy

This question has been classified as abandoned and is being closed as part of the Cleanup Program. See my comment at the end of the question for more details.