Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
3 COLOR SCALE CONDITIONAL FORMATING WITH FORMULAS
I have a worksheet that looks something like this:
A B C 1 $100 $50 $0 2 $150 $150 $75 3 $300 $200 $100 4 $200 $100 $0 5 $800 $700 $200 The values of columns B and C are independent from each other, but neither one can be larger than the value in column A (for the same row). What I want to do is to use a 3 color scale, to format the cells in column B (and also column C) according to what percentage the value of the cell represents compared to the corresponding cell in column A. So; in a scale that goes from GREEN AT 0%,to YELLOW AT 50% and RED at 100% B1 should be yellow (50%), C1 should be green (0%) B2 should be red (100%), C2 should be yellow (50%) B5 should be some shade of orange-red (87.5%) and B5 something between green and yellow (25%); and so on. I made the conditional formatting work for just one cell by making the MIN a NUMBER TYPE with value 0, then the MIDDLE POINT a FORMULA TYPE, with the formula being =$A$1/2 and the MAX was set as a FORMULA TYPE, the formula being =$A$1. But then if I just copy the format to the rest of the cells in the B column; the formulas are still referenced to cell $A$1, so the formatting formula does not work for other cells. THANK YOU. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
3 COLOR SCALE CONDITIONAL FORMATING WITH FORMULAS
Change the $A$1 reference to $A1
best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "R. Arizpe" wrote in message ... I have a worksheet that looks something like this: A B C 1 $100 $50 $0 2 $150 $150 $75 3 $300 $200 $100 4 $200 $100 $0 5 $800 $700 $200 The values of columns B and C are independent from each other, but neither one can be larger than the value in column A (for the same row). What I want to do is to use a 3 color scale, to format the cells in column B (and also column C) according to what percentage the value of the cell represents compared to the corresponding cell in column A. So; in a scale that goes from GREEN AT 0%,to YELLOW AT 50% and RED at 100% B1 should be yellow (50%), C1 should be green (0%) B2 should be red (100%), C2 should be yellow (50%) B5 should be some shade of orange-red (87.5%) and B5 something between green and yellow (25%); and so on. I made the conditional formatting work for just one cell by making the MIN a NUMBER TYPE with value 0, then the MIDDLE POINT a FORMULA TYPE, with the formula being =$A$1/2 and the MAX was set as a FORMULA TYPE, the formula being =$A$1. But then if I just copy the format to the rest of the cells in the B column; the formulas are still referenced to cell $A$1, so the formatting formula does not work for other cells. THANK YOU. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
3 COLOR SCALE CONDITIONAL FORMATING WITH FORMULAS
Excel won't let me do it, an error message appears saying that formulas in
condictional formatting cannot have realtive references (or something like that). "Bernard Liengme" wrote: Change the $A$1 reference to $A1 best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "R. Arizpe" wrote in message ... I have a worksheet that looks something like this: A B C 1 $100 $50 $0 2 $150 $150 $75 3 $300 $200 $100 4 $200 $100 $0 5 $800 $700 $200 The values of columns B and C are independent from each other, but neither one can be larger than the value in column A (for the same row). What I want to do is to use a 3 color scale, to format the cells in column B (and also column C) according to what percentage the value of the cell represents compared to the corresponding cell in column A. So; in a scale that goes from GREEN AT 0%,to YELLOW AT 50% and RED at 100% B1 should be yellow (50%), C1 should be green (0%) B2 should be red (100%), C2 should be yellow (50%) B5 should be some shade of orange-red (87.5%) and B5 something between green and yellow (25%); and so on. I made the conditional formatting work for just one cell by making the MIN a NUMBER TYPE with value 0, then the MIDDLE POINT a FORMULA TYPE, with the formula being =$A$1/2 and the MAX was set as a FORMULA TYPE, the formula being =$A$1. But then if I just copy the format to the rest of the cells in the B column; the formulas are still referenced to cell $A$1, so the formatting formula does not work for other cells. THANK YOU. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
3 COLOR SCALE CONDITIONAL FORMATING WITH FORMULAS
I entered your numbers
I selected B1:B5 In conditional formatting I used; Formula is =B1<=A1*10% and I used a yellow fill; clicked OK Cell B1 and B4 now have a yellow fill Excel did not complain about relative address Do it adding and tell use EXACTLY what the error message is best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "R. Arizpe" wrote in message ... Excel won't let me do it, an error message appears saying that formulas in condictional formatting cannot have realtive references (or something like that). "Bernard Liengme" wrote: Change the $A$1 reference to $A1 best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "R. Arizpe" wrote in message ... I have a worksheet that looks something like this: A B C 1 $100 $50 $0 2 $150 $150 $75 3 $300 $200 $100 4 $200 $100 $0 5 $800 $700 $200 The values of columns B and C are independent from each other, but neither one can be larger than the value in column A (for the same row). What I want to do is to use a 3 color scale, to format the cells in column B (and also column C) according to what percentage the value of the cell represents compared to the corresponding cell in column A. So; in a scale that goes from GREEN AT 0%,to YELLOW AT 50% and RED at 100% B1 should be yellow (50%), C1 should be green (0%) B2 should be red (100%), C2 should be yellow (50%) B5 should be some shade of orange-red (87.5%) and B5 something between green and yellow (25%); and so on. I made the conditional formatting work for just one cell by making the MIN a NUMBER TYPE with value 0, then the MIDDLE POINT a FORMULA TYPE, with the formula being =$A$1/2 and the MAX was set as a FORMULA TYPE, the formula being =$A$1. But then if I just copy the format to the rest of the cells in the B column; the formulas are still referenced to cell $A$1, so the formatting formula does not work for other cells. THANK YOU. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CONDITIONAL FORMATING: 3 COLOR SCALE REFERENCED TO A DIFFERENT CEL | Excel Discussion (Misc queries) | |||
autoshape color conditional formating | Excel Discussion (Misc queries) | |||
Change color without conditional formating | Excel Discussion (Misc queries) | |||
Conditional Formating Background Color | Excel Discussion (Misc queries) | |||
Conditional Formating - Different row color also when using filter | Excel Discussion (Misc queries) |