# Conditional Formatting - another question

#### bds

##### Board Regular
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.

### Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

#### Andrew Poulsom

##### MrExcel MVP
Example CF Formula:

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

#### Gerald Higgins

##### Well-known Member
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
Code:
``=average(A1:Z1)*.8``
and
Code:
``=average(A1:Z1)*1.2``
and format as required.

#### bds

##### Board Regular
perfect - thanks!

#### bds

##### Board Regular
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

##### Well-known Member
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

##### Board Regular
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

##### Well-known Member
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

##### Board Regular
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.

Replies
5
Views
220
Replies
4
Views
504
Replies
3
Views
203
Replies
0
Views
114
Replies
4
Views
299

1,190,656
Messages
5,982,133
Members
439,757
Latest member
85Sarah2005

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back