# Thread: Conditional Formatting Formula Help! (2002)

1. ## Conditional Formatting Formula Help! (2002)

I have a spreadhsheet containing financial data in columns D-L (Jan-Sept). I would like to have conditional formatting formulas to do the following:
1. When the September value is \$100,000 or more greater than the Jan-Aug (column D-K) average, format the September cell in Pink pattern/shading.
2. When the September value is \$100k or less than the Jan-Aug average, format the September cell in Green pattern/shading.

Can someone help with formulas to do this?????

Thanks.

2. ## Re: Conditional Formatting Formula Help! (2002)

Hans, I pasted these formulas into the worksheet. This did not work. My source data for this formatting is in a pivot table. Does this make a difference? For example, I have a row of data that is all around \$55k and consequently the average should be right at \$55k. It highlighted this September cell (L...) green...

Other ideas?

3. ## Re: Conditional Formatting Formula Help! (2002)

Can you post (a stripped down copy of) the workbook? Remove or alter sensitive data.

4. ## Re: Conditional Formatting Formula Help! (2002)

Hans, thanks. Here ya go.

5. ## Re: Conditional Formatting Formula Help! (2002)

As near as I can see, the conditional formatting is doing exactly what you said you wanted it to do. For example, the average of A6:H6 is 54,780.24. For September to be more than 100,000 greater than that it would have to be greater than 154,780.24 but it is only 55,669.37 and is therefore formatted green. If this is not what you wanted, would you give a better explanation.

6. ## Re: Conditional Formatting Formula Help! (2002)

Legare, perhaps my explanation was unclear. My attempt was to highlight in pink in situations where the September column was \$100k or more than the average of the Jan-Aug data.

For example, if Jan-Aug is 50k and Sept is 150 k, I wanted to highlight in pink (overbudget).
If Jan-Aug is \$150k and Sept is 50k I wanted to highlight in green (in the money).

I modified Han's formula to make it work.

Thanks to all...

7. ## Re: Conditional Formatting Formula Help! (2002)

Edited by HansV to correct error

Select the data values in column L. In the following, enter the formulas as if they are for the active cell. I will assume that L2 is the active cell.

Select Format | Conditional Formatting...
Select Formula Is in the first dropdown.
Enter the formula
<code>
=L2>=AVERAGE(D2:K2)+100000
</code>
Click Format...
Activate the Pattern tab.
Select a suitable pink color.
Click OK.
Select Formula Is in the first dropdown.
Enter the formula
<code>
=L2<=AVERAGE(D2:K2)-100000
</code>
Click Format...
Activate the Pattern tab.
Select a suitable green color.
Click OK.
Click OK again.

<img src=/S/whisper.gif border=0 alt=whisper width=29 height=17> It would be nice if you provided some feedback to the replies you receive. Otherwise, other Loungers have no idea whether the answers were helpful. Thank you.[ <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

8. ## Re: Conditional Formatting Formula Help! (2002)

The second formula obviously should have had -100000 instead of +100000. I hope you found that yourself. I have corrected my original reply.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•