Multiple Variables in Conditional Formatting with Excel 2003

[Lonnie Clute]

Need to created a Conditional formatting for the following:

Cells in rows J, R and Z contain data that needs the condition applied to.

Want to format the above cells with the following criteria:
The below is the criteria based on what percentage these values are off of the average value of these 3 cells
-20% to 20% Green
-30% to -20% and 20% to 30% Yellow
Less than -30% and Greater than 30% Red

I am able to perform this conditional formatting on a cell by cell basis with the following:
Condition 1:
Cell Value is Between -1.2*AC2 and 1.2*AC2 is Green
Cell Value is Between -1.2*AC2 and -1.3*AC2 is Yellow
Cell Value is Between 1.2*AC2 and 1.3*AC2 is Yellow
Cell Value is Greater than -1.3*AC2 is Red
Cell Value is Greater than 1.3*AC2 is Red

I want to see if there is a more effective way to do this against all the cells in rows J, R, and Z referenced against the average of the cells in row J, R, and Z data in all the cells in row AC

Example:
Row J Row R Row Z Row AC
12 16 8 12
Green Red Red

Let me know if you need further information and I appreciate any assistance or references you may be able to provide.


Submitted via EggHeadCafe - Software Developer Portal of Choice
How to Get Your Silverlight Pages Indexed By Search Engines
http://www.eggheadcafe.com/tutorial...e-1ca9ad6bcac8/how-to-get-your-silverlig.aspx

 

[Bob Bridges]

I'd better rephrase this, Lonnie, and see whether I have it right. You have
values in columns J, R and Z, and in each row of AC you have
AVERAGE(J2,R2,Z2). You want to color J, R and Z depending on how far off the
average it is. You don't want a single average for all the rows, ie
AVERAGE(J:J,R:R,Z:Z), but an average for each row.

Here's how I'd do it:

1) Your values in J, R and Z. You already have this.
2) AC2 (we'll use row 2 as our example) =AVERAGE(J2,R2,Z2), copied
throughout the column. You already have this too, I think.
3) Holding down the <Ctl> key, click on J, R and Z, thus highlighting the
whole of those three columns.
4) Use Format Cells to color these columns green.
5) With those three columns still highlighted, go to Conditional Formatting.
6) Set the first condition to "Formula Is" and =ABS(Z1)>ABS($AC1)*1.3. Make
the format red.
7) Add a second condition and set it to "Formula Is" and
=ABS(Z1)>ABS($AC1)*1.2. Make the format yellow.
8) Accept these, then go back to conditional formatting and make sure the
formulae are as you intended (and not with quote marks around them, which is
what often happens to me).
9) If you need to, change back the formatting on the header rows, if any.

If what you were after is a way to set this up without having to do it
cell-by-cell, I think this should do it for you.