Conditional formatting help

[matpj]

Hi there,

I have a spreadsheet containing many rows of data.
I need to format this so that the entire row appears green or red
depending on the results of a formula.

my formula in english is:

Ifcolumn K = "Development"
then IF column AT is GREATER than AP AND LESS THAN OR EQUAL to AP*1.1
then colour the row green.
else IF AT > AP*1.1 colour the row red.
Else (i.e. if K is NOT equal to "Development")

then do the same as above but replace AP*1.1 with AP*1.2

can anyone suggest how I can acheive this using conditional
formatting?
I'm unsure of where to apply the formatting and how to enter such a
formula.

thanks in advance,
Matt

 

[comish4lif]

Start by selecting the full range that you want formatted. Select
FORMAT|CONDITIONAL FORMATTING. Change Condition 1 to Formula Is (from
Cell Value is). Then enter your formula in the formula box... taking
the 2nd formula first, it should look something like this:

=AND($K1="Development",$AT1>($AP1*1.1))=TRUE
Then select the [Format] button and format as you wish (red).
Then slect [Add >>] to add a second formatting condition. It should
look something like this:

=AND($K1="Development",$AT1>$AP1)=TRUE
Then select the [Format] button and format as you wish (green).

I don't understand this part: "then do the same as above but replace
AP*1.1 with AP*1.2 "

 

[Bernard Liengme]

For conditional formatting with more than three conditions see
http://www.xldynamic.com/source/xld.CFPlus.Download.html

For just two conditions I used these formulas
=($K1="Development")*($AT1>$AP1)*($AT1<=1.1*$AP1) 'make green
and
=($K1="Development")*($AT1>1.1*$AP1) 'make red

best wishes