Conditional formatting with formulas
 

Nothing here answeres my question so I was hoping someone could help.

For example I have a cell that uses lookup formulas to grab a performance
indicator from another spreadsheet. I want the cell to change in the
following manor:

if cell equals the goal of 17 - background turns green
if cell is within 10% of 17 (above or below but not equal to 17) - turn yellow
if cell is beyond that 10% (greater than 10% above or below but not equal to
17) - turn red.

I couldn't get it to work so I just started with the first condition and I
have a cell that equals 17 exactly but does not turn green. If I type 17
over the formula whose result is 17 the cell does turn green. What am I
doing wrong? Is it the difference between "Formula is" and "Cell Value is"?
What should the formulas be to make this stop light coloring work?

Thanks again for all your help.

Conditional formatting with formulas
 

Use the Formula Is option.

Let's assume you're dealing with cell A1.

Condition 1 - GREEN
=A1=17

Condition 2 - YELLOW
=AND(A1=15.3,A1<=18.7)

Condition 3 - RED
=OR(AND(A1<"",A1<15.3),AND(COUNT(A1),A118.7))


--
Biff
Microsoft Excel MVP


"JICDB" wrote in message
...
Nothing here answeres my question so I was hoping someone could help.

For example I have a cell that uses lookup formulas to grab a performance
indicator from another spreadsheet. I want the cell to change in the
following manor:

if cell equals the goal of 17 - background turns green
if cell is within 10% of 17 (above or below but not equal to 17) - turn
yellow
if cell is beyond that 10% (greater than 10% above or below but not equal
to
17) - turn red.

I couldn't get it to work so I just started with the first condition and I
have a cell that equals 17 exactly but does not turn green. If I type 17
over the formula whose result is 17 the cell does turn green. What am I
doing wrong? Is it the difference between "Formula is" and "Cell Value
is"?
What should the formulas be to make this stop light coloring work?

Thanks again for all your help.

Conditional formatting with formulas
 

Thank you so much. I first tried condition 1 and it didn't work. I figure
you know what your talking about so what am I doing wrong. It turns out that
if I extend the number of decibles out, the answer isn't really exactly 17
but 16.99. Thank you for helping me and more importantly for restoring my
sanity - I thought I was going crazy.

"T. Valko" wrote:

Use the Formula Is option.

Let's assume you're dealing with cell A1.

Condition 1 - GREEN
=A1=17

Condition 2 - YELLOW
=AND(A1=15.3,A1<=18.7)

Condition 3 - RED
=OR(AND(A1<"",A1<15.3),AND(COUNT(A1),A118.7))


--
Biff
Microsoft Excel MVP


"JICDB" wrote in message
...
Nothing here answeres my question so I was hoping someone could help.

For example I have a cell that uses lookup formulas to grab a performance
indicator from another spreadsheet. I want the cell to change in the
following manor:

if cell equals the goal of 17 - background turns green
if cell is within 10% of 17 (above or below but not equal to 17) - turn
yellow
if cell is beyond that 10% (greater than 10% above or below but not equal
to
17) - turn red.

I couldn't get it to work so I just started with the first condition and I
have a cell that equals 17 exactly but does not turn green. If I type 17
over the formula whose result is 17 the cell does turn green. What am I
doing wrong? Is it the difference between "Formula is" and "Cell Value
is"?
What should the formulas be to make this stop light coloring work?

Thanks again for all your help.

Conditional formatting with formulas
 

Yeah, those unseen extra decimal places can reek havoc on your sanity!

So, I take it you got it sorted out? If not, just let us know. You might
have to do some sort of rounding.

--
Biff
Microsoft Excel MVP


"JICDB" wrote in message
...
Thank you so much. I first tried condition 1 and it didn't work. I
figure
you know what your talking about so what am I doing wrong. It turns out
that
if I extend the number of decibles out, the answer isn't really exactly 17
but 16.99. Thank you for helping me and more importantly for restoring my
sanity - I thought I was going crazy.

"T. Valko" wrote:

Use the Formula Is option.

Let's assume you're dealing with cell A1.

Condition 1 - GREEN
=A1=17

Condition 2 - YELLOW
=AND(A1=15.3,A1<=18.7)

Condition 3 - RED
=OR(AND(A1<"",A1<15.3),AND(COUNT(A1),A118.7))


--
Biff
Microsoft Excel MVP


"JICDB" wrote in message
...
Nothing here answeres my question so I was hoping someone could help.

For example I have a cell that uses lookup formulas to grab a
performance
indicator from another spreadsheet. I want the cell to change in the
following manor:

if cell equals the goal of 17 - background turns green
if cell is within 10% of 17 (above or below but not equal to 17) - turn
yellow
if cell is beyond that 10% (greater than 10% above or below but not
equal
to
17) - turn red.

I couldn't get it to work so I just started with the first condition
and I
have a cell that equals 17 exactly but does not turn green. If I type
17
over the formula whose result is 17 the cell does turn green. What am
I
doing wrong? Is it the difference between "Formula is" and "Cell Value
is"?
What should the formulas be to make this stop light coloring work?

Thanks again for all your help.

Conditional formatting with formulas
 

Got it - thanks to your formulas. Thanks!

"T. Valko" wrote:

Yeah, those unseen extra decimal places can reek havoc on your sanity!

So, I take it you got it sorted out? If not, just let us know. You might
have to do some sort of rounding.

--
Biff
Microsoft Excel MVP


"JICDB" wrote in message
...
Thank you so much. I first tried condition 1 and it didn't work. I
figure
you know what your talking about so what am I doing wrong. It turns out
that
if I extend the number of decibles out, the answer isn't really exactly 17
but 16.99. Thank you for helping me and more importantly for restoring my
sanity - I thought I was going crazy.

"T. Valko" wrote:

Use the Formula Is option.

Let's assume you're dealing with cell A1.

Condition 1 - GREEN
=A1=17

Condition 2 - YELLOW
=AND(A1=15.3,A1<=18.7)

Condition 3 - RED
=OR(AND(A1<"",A1<15.3),AND(COUNT(A1),A118.7))


--
Biff
Microsoft Excel MVP


"JICDB" wrote in message
...
Nothing here answeres my question so I was hoping someone could help.

For example I have a cell that uses lookup formulas to grab a
performance
indicator from another spreadsheet. I want the cell to change in the
following manor:

if cell equals the goal of 17 - background turns green
if cell is within 10% of 17 (above or below but not equal to 17) - turn
yellow
if cell is beyond that 10% (greater than 10% above or below but not
equal
to
17) - turn red.

I couldn't get it to work so I just started with the first condition
and I
have a cell that equals 17 exactly but does not turn green. If I type
17
over the formula whose result is 17 the cell does turn green. What am
I
doing wrong? Is it the difference between "Formula is" and "Cell Value
is"?
What should the formulas be to make this stop light coloring work?

Thanks again for all your help.

Conditional formatting with formulas
 

Good deal. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"JICDB" wrote in message
...
Got it - thanks to your formulas. Thanks!

"T. Valko" wrote:

Yeah, those unseen extra decimal places can reek havoc on your sanity!

So, I take it you got it sorted out? If not, just let us know. You might
have to do some sort of rounding.

--
Biff
Microsoft Excel MVP


"JICDB" wrote in message
...
Thank you so much. I first tried condition 1 and it didn't work. I
figure
you know what your talking about so what am I doing wrong. It turns
out
that
if I extend the number of decibles out, the answer isn't really exactly
17
but 16.99. Thank you for helping me and more importantly for restoring
my
sanity - I thought I was going crazy.

"T. Valko" wrote:

Use the Formula Is option.

Let's assume you're dealing with cell A1.

Condition 1 - GREEN
=A1=17

Condition 2 - YELLOW
=AND(A1=15.3,A1<=18.7)

Condition 3 - RED
=OR(AND(A1<"",A1<15.3),AND(COUNT(A1),A118.7))


--
Biff
Microsoft Excel MVP


"JICDB" wrote in message
...
Nothing here answeres my question so I was hoping someone could
help.

For example I have a cell that uses lookup formulas to grab a
performance
indicator from another spreadsheet. I want the cell to change in
the
following manor:

if cell equals the goal of 17 - background turns green
if cell is within 10% of 17 (above or below but not equal to 17) -
turn
yellow
if cell is beyond that 10% (greater than 10% above or below but not
equal
to
17) - turn red.

I couldn't get it to work so I just started with the first condition
and I
have a cell that equals 17 exactly but does not turn green. If I
type
17
over the formula whose result is 17 the cell does turn green. What
am
I
doing wrong? Is it the difference between "Formula is" and "Cell
Value
is"?
What should the formulas be to make this stop light coloring work?

Thanks again for all your help.

Conditional formatting with formulas
 

Hi

Northing wrog in the formula.

We take nos as round Nos like 17,18 and all,But Computer Will Have huge diff
in trrms of 11 or 11.0001 .Thats why your are getting this error.

Pl modify your formula value like this

if cell more than 17.001 - background turns green
if cell is within 10 to 17 (above or below but not equal to 17) - turn yellow
if cell is beyond that 9.999% (greater than 10% above or below but not equal to
17) - turn red.

Now pl try you will get the correct Format.

I have tried and found ok pl reply your Experiance.

My mailid

"JICDB" wrote:

Nothing here answeres my question so I was hoping someone could help.

For example I have a cell that uses lookup formulas to grab a performance
indicator from another spreadsheet. I want the cell to change in the
following manor:

if cell equals the goal of 17 - background turns green
if cell is within 10% of 17 (above or below but not equal to 17) - turn yellow
if cell is beyond that 10% (greater than 10% above or below but not equal to
17) - turn red.

I couldn't get it to work so I just started with the first condition and I
have a cell that equals 17 exactly but does not turn green. If I type 17
over the formula whose result is 17 the cell does turn green. What am I
doing wrong? Is it the difference between "Formula is" and "Cell Value is"?
What should the formulas be to make this stop light coloring work?

Thanks again for all your help.