IF and AND statement

[Sam Harman]

Hi, I have the following problem and want to conditionally format the
cell in question as follows:


If cell DP2 is >0 AND cell DT2<DP2 I want to conditionally format cell
DT2 with a yellow background.

This is the formula I have come up with which doesnt work.

=IF(AND(DP2>0), (DT2<DP2)

Any help appreciated

Thanks

Sam

 

[Cimjet]

Hi Sam
Very close, try this
=AND(DP2>0,DT2<DP2)
When you're working with Conditional Formatting, you're looking for a True/False
answer so you don't need the if.
Try the formula in a cell to see the result
HTH
Cimjet

 

[Sam Harman]

Thanks for the quick reply cimjet but I cant seem to get it to work
I have put this formula in for conditional formatting but it doesnt
format the cell !

Any clues?

Thanks

Sam

 

[Puppet_Sock]

Hi, I have the following problem and want to conditionally format the
cell in question as follows:

If cell DP2 is >0 AND cell DT2<DP2 I want to conditionally format cell
DT2 with a yellow background.

This is the formula I have come up with which doesnt work.

=IF(AND(DP2>0), (DT2<DP2)

Could you use an intermediate cell someplace? Say cell
DX2 has the value of DP2 if DP2 >0, and some number
that is negative with magnitude large enough such that
DT2 is always bigger, if DP2 < 0. Then your test could
be IF(DT2 < DX2).
Socks

 

[Sam Harman]

Hi Socks and thanks for the reply.

I could try that but I have loads of columns and adding in another
helper cell would not be my preferred option.

I am just wondering if the reason it doesnt work for the conditional
formatting is because the cell DT2 has a formula in it already ?

=DT2-R2

Could that be the reason?

Cheers

Sam

 

[Cimjet]

Hi Sam
A formula in a cell won't give you problems but =DT2-R2
That will give you a circular reference and should give you 0 all the time.
Could you elaborate some more.
Do you know how to use Conditional formatting?
Cimjet

 

[Sam Harman]

Hi Cimjet, yep I do know how to use conditional formatting and have
now done as socks suggested and used a helper row to carry out the
calculation so that DT2 is now the result of the helper row....

However, the good new is that the formula now works in part, it is
highlighting some of the cells where the DT2 number is less than the
DP2 number but not all, and it is also highlighting it where the DT2
number and DP2 number are the same !!!! even though the forumla
clearly says < than..

Any clues

Cheers

Sam

 

[Cimjet]

Select one of the cells that is wrong and go see in Conditional formatting what
the formula looks like.
Without the file, it's very hard to know what's happening.
Cimjet

 

[Pohlepny]

Hi,

this should work...

=AND(DP2>0;AND(DT2<DP2))

--
P.

"Sam Harman" wrote in message

Thanks for the quick reply cimjet but I cant seem to get it to work
I have put this formula in for conditional formatting but it doesnt
format the cell !

Any clues?

Thanks

Sam

 

[Sam Harman]

Yes sorry I appreciate that it is difficult

What I can tell you is this is an example from two lines of the table

R DP DQ DR = DP-DQ) DS DT = (DP-Rr)
0 79 80 -1 -1 79
3 74 76 -2 -2 71

Ideally I would like to conditionally format DT line two as the value
of this line (71) is less than DP (74). The first line would not be
formatted as the value of DTand DP is the same. Also if any cell in DP
has a value of 0 I want to ignore that completely regardless of what
value is in cell DT

Hope that helps


Regards

Sam