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

Try

=ABS(H7-AVERAGE(B7:G7))<=2

If this post helps click Yes
---------------
Jacob Skaria


"murkaboris" wrote:

> 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

shoud be in %

=ABS(H7-AVERAGE(B7:G7))<=2%

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

> Try
>
> =ABS(H7-AVERAGE(B7:G7))<=2
>
> If this post helps click Yes
> ---------------
> Jacob Skaria
>
>
> "murkaboris" wrote:
>
> > 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

"Jacob Skaria" wrote:

> shoud be in %
>
> =ABS(H7-AVERAGE(B7:G7))<=2%
>
> If this post helps click Yes
> ---------------
> Jacob Skaria
>
>
> "Jacob Skaria" wrote:
>
> > Try
> >
> > =ABS(H7-AVERAGE(B7:G7))<=2
> >
> > If this post helps click Yes
> > ---------------
> > Jacob Skaria
> >
> >
> > "murkaboris" wrote:
> >
> > > 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

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 Skaria


"murkaboris" wrote:

> 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
>
> "Jacob Skaria" wrote:
>
> > shoud be in %
> >
> > =ABS(H7-AVERAGE(B7:G7))<=2%
> >
> > If this post helps click Yes
> > ---------------
> > Jacob Skaria
> >
> >
> > "Jacob Skaria" wrote:
> >
> > > Try
> > >
> > > =ABS(H7-AVERAGE(B7:G7))<=2
> > >
> > > If this post helps click Yes
> > > ---------------
> > > Jacob Skaria
> > >
> > >
> > > "murkaboris" wrote:
> > >
> > > > 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

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 Skaria" wrote:

> 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 Skaria
>
>
> "murkaboris" wrote:
>
> > 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
> >
> > "Jacob Skaria" wrote:
> >
> > > shoud be in %
> > >
> > > =ABS(H7-AVERAGE(B7:G7))<=2%
> > >
> > > If this post helps click Yes
> > > ---------------
> > > Jacob Skaria
> > >
> > >
> > > "Jacob Skaria" wrote:
> > >
> > > > Try
> > > >
> > > > =ABS(H7-AVERAGE(B7:G7))<=2
> > > >
> > > > If this post helps click Yes
> > > > ---------------
> > > > Jacob Skaria
> > > >
> > > >
> > > > "murkaboris" wrote:
> > > >
> > > > > 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

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?
--
David Biddulph


"murkaboris" <(E-Mail Removed)> wrote in message
news:BAAF43FC-EC74-45B5-ABFA-(E-Mail Removed)...
> 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 Skaria" wrote:
>
>> 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 Skaria
>>
>>
>> "murkaboris" wrote:
>>
>> > 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
>> >
>> > "Jacob Skaria" wrote:
>> >
>> > > shoud be in %
>> > >
>> > > =ABS(H7-AVERAGE(B7:G7))<=2%
>> > >
>> > > If this post helps click Yes
>> > > ---------------
>> > > Jacob Skaria
>> > >
>> > >
>> > > "Jacob Skaria" wrote:
>> > >
>> > > > Try
>> > > >
>> > > > =ABS(H7-AVERAGE(B7:G7))<=2
>> > > >
>> > > > If this post helps click Yes
>> > > > ---------------
>> > > > Jacob Skaria
>> > > >
>> > > >
>> > > > "murkaboris" wrote:
>> > > >
>> > > > > 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

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


"David Biddulph" wrote:

> 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?
> --
> David Biddulph
>
>
> "murkaboris" <(E-Mail Removed)> wrote in message
> news:BAAF43FC-EC74-45B5-ABFA-(E-Mail Removed)...
> > 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 Skaria" wrote:
> >
> >> 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 Skaria
> >>
> >>
> >> "murkaboris" wrote:
> >>
> >> > 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
> >> >
> >> > "Jacob Skaria" wrote:
> >> >
> >> > > shoud be in %
> >> > >
> >> > > =ABS(H7-AVERAGE(B7:G7))<=2%
> >> > >
> >> > > If this post helps click Yes
> >> > > ---------------
> >> > > Jacob Skaria
> >> > >
> >> > >
> >> > > "Jacob Skaria" wrote:
> >> > >
> >> > > > Try
> >> > > >
> >> > > > =ABS(H7-AVERAGE(B7:G7))<=2
> >> > > >
> >> > > > If this post helps click Yes
> >> > > > ---------------
> >> > > > Jacob Skaria
> >> > > >
> >> > > >
> >> > > > "murkaboris" wrote:
> >> > > >
> >> > > > > 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
>
>
> .
>

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


"murkaboris" <(E-Mail Removed)> wrote in message
news:FA1962E4-BC0C-4C23-8D59-(E-Mail Removed)...
> 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
>
>
> "David Biddulph" wrote:
>
>> 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?
>> --
>> David Biddulph
>>
>>
>> "murkaboris" <(E-Mail Removed)> wrote in message
>> news:BAAF43FC-EC74-45B5-ABFA-(E-Mail Removed)...
>> > 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 Skaria" wrote:
>> >
>> >> 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 Skaria
>> >>
>> >>
>> >> "murkaboris" wrote:
>> >>
>> >> > 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
>> >> >
>> >> > "Jacob Skaria" wrote:
>> >> >
>> >> > > shoud be in %
>> >> > >
>> >> > > =ABS(H7-AVERAGE(B7:G7))<=2%
>> >> > >
>> >> > > If this post helps click Yes
>> >> > > ---------------
>> >> > > Jacob Skaria
>> >> > >
>> >> > >
>> >> > > "Jacob Skaria" wrote:
>> >> > >
>> >> > > > Try
>> >> > > >
>> >> > > > =ABS(H7-AVERAGE(B7:G7))<=2
>> >> > > >
>> >> > > > If this post helps click Yes
>> >> > > > ---------------
>> >> > > > Jacob Skaria
>> >> > > >
>> >> > > >
>> >> > > > "murkaboris" wrote:
>> >> > > >
>> >> > > > > 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
>>
>>
>> .
>>

Thank you David, this solved it.
Monika

"David Biddulph" wrote:

> 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
>
>
> "murkaboris" <(E-Mail Removed)> wrote in message
> news:FA1962E4-BC0C-4C23-8D59-(E-Mail Removed)...
> > 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
> >
> >
> > "David Biddulph" wrote:
> >
> >> 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?
> >> --
> >> David Biddulph
> >>
> >>
> >> "murkaboris" <(E-Mail Removed)> wrote in message
> >> news:BAAF43FC-EC74-45B5-ABFA-(E-Mail Removed)...
> >> > 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 Skaria" wrote:
> >> >
> >> >> 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 Skaria
> >> >>
> >> >>
> >> >> "murkaboris" wrote:
> >> >>
> >> >> > 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
> >> >> >
> >> >> > "Jacob Skaria" wrote:
> >> >> >
> >> >> > > shoud be in %
> >> >> > >
> >> >> > > =ABS(H7-AVERAGE(B7:G7))<=2%
> >> >> > >
> >> >> > > If this post helps click Yes
> >> >> > > ---------------
> >> >> > > Jacob Skaria
> >> >> > >
> >> >> > >
> >> >> > > "Jacob Skaria" wrote:
> >> >> > >
> >> >> > > > Try
> >> >> > > >
> >> >> > > > =ABS(H7-AVERAGE(B7:G7))<=2
> >> >> > > >
> >> >> > > > If this post helps click Yes
> >> >> > > > ---------------
> >> >> > > > Jacob Skaria
> >> >> > > >
> >> >> > > >
> >> >> > > > "murkaboris" wrote:
> >> >> > > >
> >> >> > > > > 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
> >>
> >>
> >> .
> >>
>
>
> .
>