Conditional Formating
 

What I have is a simple spread sheet with the months accross the first row
and and the sum and average of product sold at the end of each row. What I
would like to do is look at each cell and compare it to the average and the
end of the cell. If it is lower change the color of that cell. I have
attempted the conditional formating but cannot get it to work. Any help
would be appreciated.

Dec-04 Jan-05 Feb-05 Mar-05 Sum Average
286 535 658 898 2,378 594
1,063 1,495 1,543 1,506 5,607 1402
919 870 834 820 3,444 861
1,055 1,219 1,150 1,173 4,597 1149
1,055 1,511 787 1,028 4,380 1095
1,363 1,750 1,077 1,293 5,483 1371
852 576 576 599 2,603 651
1,301 1,215 1,387 1,430 5,332 1333
783 664 687 707 2,841 710
660 631 639 628 2,559 640
1,698 1,154 1,104 1,065 5,021 1255
2,000 1,480 1,348 1,316 6,142 1536
1,004 1,018 1,121 1,179 4,322 1080
1,151 1,031 1,158 1,168 4,508 1127
781 480 477 482 2,220 555
2,730 2,788 2,898 2,833 11,249 2812
1,065 895 987 1,125 4,072 1018

Select the columns for the months - looks like A:D
If A2 is the active cell, use Conditional Formatting, change "Cell Value Is"
to "Formula Is" and enter:
=A2<$F2
("$" usage important), then click the Format button & pick your color...

Bob Umlas
Excel MVP

"Daniell" wrote in message
...
What I have is a simple spread sheet with the months accross the first row
and and the sum and average of product sold at the end of each row. What
I
would like to do is look at each cell and compare it to the average and
the
end of the cell. If it is lower change the color of that cell. I have
attempted the conditional formating but cannot get it to work. Any help
would be appreciated.

Dec-04 Jan-05 Feb-05 Mar-05 Sum Average
286 535 658 898 2,378 594
1,063 1,495 1,543 1,506 5,607 1402
919 870 834 820 3,444 861
1,055 1,219 1,150 1,173 4,597 1149
1,055 1,511 787 1,028 4,380 1095
1,363 1,750 1,077 1,293 5,483 1371
852 576 576 599 2,603 651
1,301 1,215 1,387 1,430 5,332 1333
783 664 687 707 2,841 710
660 631 639 628 2,559 640
1,698 1,154 1,104 1,065 5,021 1255
2,000 1,480 1,348 1,316 6,142 1536
1,004 1,018 1,121 1,179 4,322 1080
1,151 1,031 1,158 1,168 4,508 1127
781 480 477 482 2,220 555
2,730 2,788 2,898 2,833 11,249 2812
1,065 895 987 1,125 4,072 1018

It should work just fine. With your cursor selecting the cell with the "286"
go to Format-Conditional Formatting. Leave "Cell Value Is", change the next
drop down to "is less than" and in next box enter (or point to) the cell with
the average figure. Then click on Format and change the cell color to
whatever you want. Finish off by clicking OK. If you want conditional
formatting in all the cells and you're going to do that by copying and
pasting, be sure to use paste-special-format, and also be sure that your cell
references are correct (absolute or relative)... it makes all the difference
in the world. Hope that helps.
--
Stan


"Daniell" wrote:

What I have is a simple spread sheet with the months accross the first row
and and the sum and average of product sold at the end of each row. What I
would like to do is look at each cell and compare it to the average and the
end of the cell. If it is lower change the color of that cell. I have
attempted the conditional formating but cannot get it to work. Any help
would be appreciated.

Dec-04 Jan-05 Feb-05 Mar-05 Sum Average
286 535 658 898 2,378 594
1,063 1,495 1,543 1,506 5,607 1402
919 870 834 820 3,444 861
1,055 1,219 1,150 1,173 4,597 1149
1,055 1,511 787 1,028 4,380 1095
1,363 1,750 1,077 1,293 5,483 1371
852 576 576 599 2,603 651
1,301 1,215 1,387 1,430 5,332 1333
783 664 687 707 2,841 710
660 631 639 628 2,559 640
1,698 1,154 1,104 1,065 5,021 1255
2,000 1,480 1,348 1,316 6,142 1536
1,004 1,018 1,121 1,179 4,322 1080
1,151 1,031 1,158 1,168 4,508 1127
781 480 477 482 2,220 555
2,730 2,788 2,898 2,833 11,249 2812
1,065 895 987 1,125 4,072 1018

I attempted both ways and have had a problem with both. The cells do not
change the way it should. The average cell is a formula could this be
causing a problem?

"Bob Umlas" wrote:

Select the columns for the months - looks like A:D
If A2 is the active cell, use Conditional Formatting, change "Cell Value Is"
to "Formula Is" and enter:
=A2<$F2
("$" usage important), then click the Format button & pick your color...

Bob Umlas
Excel MVP

"Daniell" wrote in message
...
What I have is a simple spread sheet with the months accross the first row
and and the sum and average of product sold at the end of each row. What
I
would like to do is look at each cell and compare it to the average and
the
end of the cell. If it is lower change the color of that cell. I have
attempted the conditional formating but cannot get it to work. Any help
would be appreciated.

Dec-04 Jan-05 Feb-05 Mar-05 Sum Average
286 535 658 898 2,378 594
1,063 1,495 1,543 1,506 5,607 1402
919 870 834 820 3,444 861
1,055 1,219 1,150 1,173 4,597 1149
1,055 1,511 787 1,028 4,380 1095
1,363 1,750 1,077 1,293 5,483 1371
852 576 576 599 2,603 651
1,301 1,215 1,387 1,430 5,332 1333
783 664 687 707 2,841 710
660 631 639 628 2,559 640
1,698 1,154 1,104 1,065 5,021 1255
2,000 1,480 1,348 1,316 6,142 1536
1,004 1,018 1,121 1,179 4,322 1080
1,151 1,031 1,158 1,168 4,508 1127
781 480 477 482 2,220 555
2,730 2,788 2,898 2,833 11,249 2812
1,065 895 987 1,125 4,072 1018