Hi,
Sorry about the tongue twister of a title.
I am trying to conditionally format a cell with the following parameters:
If empty no formatting
If cell value = 0 then turn orange
If cell value < 0 then turn red
If cell value > 0 then turn green
I know this is technically 4 conditions (of which excel 2003 accepts only 3) but I feel that there must be a formula of some sort that could achieve this.
Thanks in advance.
-Upside
Re: 4 Conditions For Conditional Formatting
This is technically three conditional formats.
Re: 4 Conditions For Conditional Formatting
The problem is an empty cell is returned as a 0 therefore the condition to turn the cell orange when 0 is true means that it is always orange when I don't want any formatting applied.
Re: 4 Conditions For Conditional Formatting
Add IsNumber() to the conditional format in testing for zero.
Re: 4 Conditions For Conditional Formatting
is this correct?
cells default color= orange
conditional format3 : cell value=and(isnumber(a1),0) : no color
regards,
Re: 4 Conditions For Conditional Formatting
That's close, junho.
Re: 4 Conditions For Conditional Formatting
thanks!
Re: 4 Conditions For Conditional Formatting
Its also possible to use custom number formats to some extent, eg:
Format>Cells>Custom> [Blue][=0]#,##0;[Red][<0]-#,##0;[Green] #,##0;[Magenta]@
This would make zero values BLUE, negative numbers RED, positive numbers GREEN, text MAGENTA
Conditional Formating could also be used that would overide the cell formating, eg:
Condition1: CellValueIs 0 (Orange)
Condition2: FormulaIs: =AND(ISNUMBER(A1), A1>=100) (Aqua)
Condition3: FormulaIs: =AND(ISNUMBER(A1), A1>=10) (Lavender)
Perhaps read here for more http://www.ozgrid.com/Excel/font-formats.htm
Re: 4 Conditions For Conditional Formatting
Another solution (I think more elegant in respect that it would negate the need for two extra helper columns in my sheet) would be comparing the Vlookup values of two cells (BI5 & BM5) which is then used to conditionally format the cells:
=IF(VLOOKUP(BI5,VLookupTable!$G$2:$H$7,2)>=(VLOOKUP(BM5,VLookupTable!$G$2:$H$7,2)))
the above would turn green, OR
=IF(VLOOKUP(BI5,VLookupTable!$G$2:$H$7,2)<(VLOOKUP(BM5,VLookupTable!$G$2:$H$7,2)))
would turn red.
It returns an error however - can someone spot the mistake?
Re: 4 Conditions For Conditional Formatting
Your syntax, [COLOR="Blue"]=IF(Condition)[/COLOR], returns 0 if Condition is true and FALSE if Condition is false.
You could use [COLOR="Blue"]=SIGN(A-B)[/COLOR], which would return -1, 0, or 1.
Re: 4 Conditions For Conditional Formatting
Thanks
Don’t have an account yet? Register yourself now and be a part of our community!
