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

[[email protected]]

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.....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
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"matpj" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> matpj
> ------------------------------------------------------------------------
> matpj's Profile:
> http://www.excelforum.com/member.php...o&userid=21076
> View this thread: http://www.excelforum.com/showthread...hreadid=509358
>