Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Custom number formats - Selective cases
Hi, today I watched a webcast for easy charts on excel, in there it was
mentioned that you can use formulas to change the color of a data label customizing the numbers, my question is: How can I use it to define for a section? (I need to get red colored for values upper than 10%, yellow for values between 10 and 8%, and normal for below) I tryied based on my experince to use the following formulas: [red][0.1]0%;[yellow][<0.1 and 0.08]0%;[black]0% but doesn't work Tha actual formula i'm using is the following: [red][0.1]0%;[black]0% I don't want to use macros, this is just to learn the way to do it different. Thank you. |
#2
|
|||
|
|||
"Dragospeed" wrote: Hi, today I watched a webcast for easy charts on excel, in there it was mentioned that you can use formulas to change the color of a data label customizing the numbers, my question is: How can I use it to define for a section? (I need to get red colored for values upper than 10%, yellow for values between 10 and 8%, and normal for below) I tryied based on my experince to use the following formulas: [red][0.1]0%;[yellow][<0.1 and 0.08]0%;[black]0% but doesn't work Tha actual formula i'm using is the following: [red][0.1]0%;[black]0% I don't want to use macros, this is just to learn the way to do it different. Thank you. Hi Dragospeed I think you can use Conditional Formatting. Click Format, Conditional Formatting. Then using More Than, Less Than, Between, etc - select a color to format the font when the condition is met. You will need to use the Conditional Formatting in each cell. You could also conditional format the background color and borders if you wanted to. If More Than, Less Than, etc don't work, you can refer to any cell for the condition. Ed |
#3
|
|||
|
|||
Am I correct in assuming your question has nothing to to do with charts or graphs.
The format you have shown is like for custom formats but they are not extensible. basically you get positive, negative, and zero for numbers and then text. My mistake you have three categories, but can't use and with custom formatting. You have to use Conditional formatting see http://www.mvps.org/dmcritchie/excel/condfmt.htm Select the cells to be colored (formatted) Then your formula is based on the active cell so if C1 is your active cell. C.F. 1, formula is: =AND(C1.08, C1<=0.1) format as desired CF. 1, formula is : =C1.1 You will have to do the number format with regular number formatting but the coloring will be done with C.F. I'm tired you might be able to use normal cell formatting first use 1 then for second part .08 then everything else -- --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Dragospeed" wrote in message ... Hi, today I watched a webcast for easy charts on excel, in there it was mentioned that you can use formulas to change the color of a data label customizing the numbers, my question is: How can I use it to define for a section? (I need to get red colored for values upper than 10%, yellow for values between 10 and 8%, and normal for below) I tryied based on my experince to use the following formulas: [red][0.1]0%;[yellow][<0.1 and 0.08]0%;[black]0% but doesn't work Tha actual formula i'm using is the following: [red][0.1]0%;[black]0% I don't want to use macros, this is just to learn the way to do it different. Thank you. |
#4
|
|||
|
|||
Okay couldn't sleep either, you can do it with
Normal cell formatting: format, cells, custom [Red][0.1]0%;[Yellow][0.08]0%;0%;@ but if you really expect to see a yellow font, you will probably have to make the background of the column gray. If you want interior (background) formatting you are back to using Conditional Formatting. I would suggest blue instead of yellow. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Custom Formats | Excel Discussion (Misc queries) | |||
Custom Formats | Excel Discussion (Misc queries) | |||
number formats that allow you to format the appearance of negativ. | Charts and Charting in Excel | |||
Nested IF limit or Open parentheses limit | Excel Discussion (Misc queries) | |||
How to format a number in Indian style in Excel? | Excel Discussion (Misc queries) |