Conditional Formatting - another question

[bds]

I would like to assign a different color to cells that are 20% above or 20% below the average for a row of data. I don't know how to do this. I have Excel 2002. Any help appreciated.

 

[Andrew Poulsom]

Example CF Formula:

=A1>(AVERAGE(A:A)*120%)

 

[Gerald Higgins]

Let's assume your data is in row 1, cols A to Z (you did say a ROW of data).
This time use Cell Value Is, Between

=average(A1:Z1)*.8

and

=average(A1:Z1)*1.2

and format as required.

 

[bds]

perfect - thanks!

 

[bds]

2 more questions:

How do I exclude blank cells from the formatting?

How do I copy the conditional formatting to another row or rows? For example, for the row below, I want the same formula for only for those rows cause they are different info. I could enter it for each row, but seems there should be an easier way. When I select all the rows, it averages all the data in all the rows instead of just in that row.

 

[Gerald Higgins]

When you say exclude blank cells from the formatting, do you actually mean exclude blank cells from calculation of the averages ?

For copying the formating to other rows, you can use Copy, Paste Special, Formats, to paste conditional formating to somewhere else. If it's calculating the averages wrong, I need to ask, are you using my suggested solution or Andrew Poulsom's ? They are different, and I think mine will work the way you want it, to copy down to other rows.

 

[bds]

Gerald,

I used your suggestion -(tried it first and it worked). It is now working fine (hadn't thought of copy, paste special). However, one new wrinkle.

I had a conditional format on the sheet to highlight alternate rows. The formula I used was Formula Is = MOD(ROW(),2)=0. When I add this to the other 2 lines of condtional formats, I can't get all three things to happen. For example, I wipe out the colored text I am using to represent cells above or below the average if I put this formula first in the conditional formatting drop down. I've tried putting the Conditions in different order, and I've tried using different color background and/or different color test. No matter what I try, seems I can have the averages stand out OR can have alternate rows colored different ways for ease of reading. Any thoughts?

 

[Gerald Higgins]

I'm not sure, but it sounds as if you might be getting confused by conflicting conditions.

Try thinking of it this way.

Let's say you have two conditions, and two formats you want to apply.
Condition 1
If A1>A2, make text red on a yellow background
Condition 2
If A1>100, make text blue on a green background.
Let's say your default format is black text on a white background.

There are then four possible outcomes -
Outcome 1, A1=1, A2=2, Condition 1 is false, Condition 2 is false, Excel will apply the default format, black text on a white background.
Outcome 2, A1=2, A2=1, Condition 1 is true, Condition 2 is false, Excel will make text red on a yellow background.
Outcome 3, A1=101, A2=102, Condition 1 is false, Condition 2 is true, Excel will make text blue on a green background.
Outcome 4, A1=102, A2=101, Condition 1 is true AND Condition 2 is true.
Excel will base its formating on whichever true condition comes first.
In this case, it will apply the formating for Condition 1 and give you red text on a yellow background. If you reverse the order of the Conditions, it will give you blue text on a black background.

What you need to do now, is think about all the different ways that your conditions could be true / not true, and then build your conditional formating to reflect that.
It is usually simplest if your conditions do not overlap, i.e. only one can be true at a time.
If it is possible for several conditions to be true at a time, you can use Conditional Formating to reflect this, but you need to be more careful about how you set it up. One way to help you do this is probably to take the set of circumstances that relate to two or more conditions being true, and make that your highest priority condition.

 

[bds]

Gerald,

You are exactly right about what is happening. I've been fiddling and found that I can do - not as easy to read - but readable - is to make the first line of the conditional format read so that it puts a border above and below everh row, and then two more lines to use different color text for when the above or below average conditions are met. Thanks for explaning how excel thinks.