Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formating | Excel Discussion (Misc queries) | |||
Help using Conditional Formating of Entire Rows | Excel Worksheet Functions | |||
more than 3 conditional formating in excel | Excel Discussion (Misc queries) | |||
Conditional Formating when result is text | Excel Worksheet Functions | |||
30 Day Aging Report Using Conditional Formating | Excel Worksheet Functions |