format cells depending on value range
in [General]
|
Prev: Data validation
Next: Mysteries on msnews.
From: gashman on 25 May 2010 09:54 Using Excel 2003 and am trying to format cells depending on a range of values e.g. If the value is less than 50.999% then colour the cell red If the value is between 51% and 74.999% then colour the cell orange If the value is more than 75% then colour the cell green I have managed to get the first and third examples to work using conditional formatting (formula is: =B4<0.50999) but can't seem to get it to work if the value is between two specific values. Any help would be appreciated.
From: Stefi on 25 May 2010 10:04 Maybe =B4<51% for red =AND(B4>=51%,B4<75%) for orange =B4>=75% for green -- Regards! Stefi „gashman” ezt írta: > Using Excel 2003 and am trying to format cells depending on a range of values > e.g. > > If the value is less than 50.999% then colour the cell red > > If the value is between 51% and 74.999% then colour the cell orange > > If the value is more than 75% then colour the cell green > > I have managed to get the first and third examples to work using conditional > formatting (formula is: =B4<0.50999) but can't seem to get it to work if the > value is between two specific values. > > Any help would be appreciated.
From: T. Valko on 25 May 2010 10:09 Try it like this... =AND(cell_ref>lower_boundary_value,cell_ref<upper_boundary_value) Or... =AND(cell_ref>=lower_boundary_value,cell_ref<=upper_boundary_value) -- Biff Microsoft Excel MVP "gashman" <gashman(a)discussions.microsoft.com> wrote in message news:25789EF0-9EAF-44A7-83A3-5571D2B451AD(a)microsoft.com... > Using Excel 2003 and am trying to format cells depending on a range of > values > e.g. > > If the value is less than 50.999% then colour the cell red > > If the value is between 51% and 74.999% then colour the cell orange > > If the value is more than 75% then colour the cell green > > I have managed to get the first and third examples to work using > conditional > formatting (formula is: =B4<0.50999) but can't seem to get it to work if > the > value is between two specific values. > > Any help would be appreciated.
From: Jim Thomlinson on 25 May 2010 10:32 CF's are evaluated in order. As soon as a conditon is met it stops evaluating so all you need is Less than 51% Less than 75% Greater than or equal to 75% -- HTH... Jim Thomlinson "gashman" wrote: > Using Excel 2003 and am trying to format cells depending on a range of values > e.g. > > If the value is less than 50.999% then colour the cell red > > If the value is between 51% and 74.999% then colour the cell orange > > If the value is more than 75% then colour the cell green > > I have managed to get the first and third examples to work using conditional > formatting (formula is: =B4<0.50999) but can't seem to get it to work if the > value is between two specific values. > > Any help would be appreciated.
From: Jacob Skaria on 25 May 2010 11:07
Use the below formulas for a selected range say A1:A10 with active cell A1. =A1<50.999% =MEDIAN(51%,A1,74.999%)=A1 =A1>75% -- Jacob (MVP - Excel) "gashman" wrote: > Using Excel 2003 and am trying to format cells depending on a range of values > e.g. > > If the value is less than 50.999% then colour the cell red > > If the value is between 51% and 74.999% then colour the cell orange > > If the value is more than 75% then colour the cell green > > I have managed to get the first and third examples to work using conditional > formatting (formula is: =B4<0.50999) but can't seem to get it to work if the > value is between two specific values. > > Any help would be appreciated. |