Conditional Formatting with average formula and variance

  • Thread starter Thread starter murkaboris
  • Start date Start date
M

murkaboris

Hello:

Can somebody please advise how to include a variance in conditional
formating when using average?

i.e. I'd like to highlight cells in red if the average of a range is +/- 2%
of the number (so if H7 is 24% and the average of B7:G7 is 28% the H7 cell
will be in red and if average of B7:G7 is 25%, which is within the 2%
variance the H7 cell will be in green).

Simple average worked (=H7>AVERAGE(B7:G7) --- red)....but can't figure out
how to include the variance of 2% up and down from average...

Thank you.

Monika
 
Hi Jacob:

It seems to be working. I have to fully populate the rest of the xls but the
test on couple of rows worked.
thank you for your quick response.

Monika
 
1. Select the Range (say H1:H100) or column H. Please note that the cell
reference H1 mentioned in the formula is the active cell in the selection.
Active cell will have a white background even after selection

2. From menu Format>Conditional Formatting>

3. For Condition1>Select 'Formula Is' and enter the below formula
=ABS(H1-AVERAGE(B1:G1))<=2%

4. Click Format Button>Pattern and select your color (say Green)
5. Hit OK

PS: If you are using XL2007 Goto Home tab>Styles>Conditional
Formatting>Manage rules>New rule>Use a formula to determine which cells to
format. Enter the formula in the box below.

If this post helps click Yes
 
Jacob:

It worked partially. I need it to evaluate the average and if its within 2%
make it green but if its outside of the 2% make it red.

The formula you gave me works if the cell is less than 2% of the average but
not if its more. I removed the "=" sign bcs if its 2% off of the average is
still ok just anythign abover or below. I've tried to change it to the
following but it doesn't work:

=ABS(H27-AVERAGEA(B27:G27))<>2%

any ideas?

Thank you
Monika
 
Jacob's formula works correctly. Your formula won't work, because you are
testing for the difference not being exactly equal to 2%
What numbers do you have in which of your cells (B to H), what result did
you get from Jacob's formula, & what result did you expect?

As a matter of interest, why did you change from AVERAGE to AVERAGEA? Do
you have non-numeric values in some of your cells?
 
Hello David:

the AVERAGEA was just a type my formula actually reads "AVERAGE" -- sorry.
So using the formula given my average of a row in cells from B to G comes to
23.5% and my H cell is 28% so based on the formula it should be read bcs the
difference is more than 2% from the 23.5% average but using the formula it
keeps it black. Same if it is below. The only time it makes it red is if its
withing those 2%. ie. if my cell H is 25% than the conditional formatting
makes it red but I need it the other way around.

B27 = 24%
C27 = 21%
D27 = 23%
E27 = 22%
F27 = 26%
G27 = 26%

H27 --- for test purposes I used 21% to test the lover range which should
have changed the number to red but didn't, 28% also should be highlighted but
didn't and 25% which is in the 2% range should have stayed black but that's
the one that changed to red.

Thanks
Monika

B
 
Your condition for green is
=ABS(H27-AVERAGE(B27:G27))<=2%

Your condition for red is
=ABS(H27-AVERAGE(B27:G27))>2%
though in fact you can get away with just formatting the cell as red and
then using CF for your green condition.
 
Thank you David, this solved it.
Monika

David Biddulph said:
Your condition for green is
=ABS(H27-AVERAGE(B27:G27))<=2%

Your condition for red is
=ABS(H27-AVERAGE(B27:G27))>2%
though in fact you can get away with just formatting the cell as red and
then using CF for your green condition.
--
David Biddulph





.
Click to expand...
 
Back
Top