Conditional Formatting with average formula and variance

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 (=H7AVERAGE(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 (=H7AVERAGE(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 (=H7AVERAGE(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 (=H7AVERAGE(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 FormatConditional Formatting

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

4. Click Format ButtonPattern and select your color (say Green)
5. Hit OK

PS: If you are using XL2007 Goto Home tabStylesConditional
FormattingManage rulesNew ruleUse 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 (=H7AVERAGE(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 FormatConditional Formatting

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

4. Click Format ButtonPattern and select your color (say Green)
5. Hit OK

PS: If you are using XL2007 Goto Home tabStylesConditional
FormattingManage rulesNew ruleUse 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 (=H7AVERAGE(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" wrote in message
...
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 FormatConditional Formatting

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

4. Click Format ButtonPattern and select your color (say Green)
5. Hit OK

PS: If you are using XL2007 Goto Home tabStylesConditional
FormattingManage rulesNew ruleUse 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 (=H7AVERAGE(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" wrote in message
...
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 FormatConditional Formatting

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

4. Click Format ButtonPattern and select your color (say Green)
5. Hit OK

PS: If you are using XL2007 Goto Home tabStylesConditional
FormattingManage rulesNew ruleUse 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 (=H7AVERAGE(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" wrote in message
...
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" wrote in message
...
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 FormatConditional Formatting

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

4. Click Format ButtonPattern and select your color (say Green)
5. Hit OK

PS: If you are using XL2007 Goto Home tabStylesConditional
FormattingManage rulesNew ruleUse 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 (=H7AVERAGE(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" wrote in message
...
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" wrote in message
...
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 FormatConditional Formatting

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

4. Click Format ButtonPattern and select your color (say Green)
5. Hit OK

PS: If you are using XL2007 Goto Home tabStylesConditional
FormattingManage rulesNew ruleUse 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 (=H7AVERAGE(B7:G7) --- red)....but
can't
figure out
how to include the variance of 2% up and down from average...

Thank you.

Monika


.



.