Conditional Formatting

[LaurieZ]

Hello...I need help on conditional formatting. Here's what I have...
I have an excel worksheet that contains columns by month of the financials for all our customers. Then, I have a total column of the for the year and anther column with the monthly average.
The worksheet also includes a variance threshold where the user puts in a $$ amt.
I need a way to highlight certain cells if the actual monthly amount is less than or greater than a variable amount of the calculated average. So, for example the monthly average for customer A is 46.00 but I want to color all the cells for all customers whose monthly average is $25 less or greater than the calculated average for that customer
Here’s an example of worksheet…

VARIANCE (+ or - the 2012 average) = 25.00 (this is a variable that the user enters...can be any $$ amount)
Cust A Jan 2012 Feb 2012 Mar 2012 Apr 2012 May 2012 Jun 2012 Total 2012 Avg
40 21 43 38 89 44 276 46

So for the above, I would need the months of Feb and May highlighted as the as they are $25 below or above the 2012 average of $46.
Any suggestions are greatly appreciated....it sounded easy but has proven to been a little more complicated!

Thank you
Laurie

[FDibbins]

Hi and welcome to the forum

This sounds relatively straightforward, but to create the Conditional Formatting rule I will need to see a sample of what you are working with. Often, copy/paste to here doesnt work so well

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[LaurieZ]

sample worksheet attached....thank you!

[FDibbins]

So for the above, I would need the months of Feb and May highlighted as the as they are $25 below or above the 2012 average of $46.

25 greater and less than 46 is 21 (lower range) and 71 (upper range)

Feb = 21 + 25= 46 (within both ranges)
Feb = 21 - 25= -4 (outside lower range)
May = 89 + 25 = 114 (outside upper range)
May = 89 - 25 = 64 (inside upper range)

Based on these, niether would qualify?

[LaurieZ]

I've attached another file with more detailed examples and explanation.......thanks again!