Excel Conditional Formatting not working on one cell

[TJVFree]

Hi, I’m working with a spread sheet that has a couple of formulas. One of the formula is 90%.
For some reason I’m not able to get the conditional formatting for work on this cell.

Would someone take a look and let me know what I’m doing wrong

TCB

 

[SkipVought]

Hi,

What do you expect to conditional format to do to your 0.899999?

 

[TJVFree]

Total 90 in the First Party Contact and up towards the top when the cell turns into 90% turn yellow

TCB

 

[SkipVought]

Well it's less than .9!

You might want to round up in the CF formula criteria.

 

[TJVFree]

That didn't seem to work either

TCB

 

[SkipVought]

Exactly what did you do that did not work?

 

[randy700]

when the cell turns into 90% turn yellow

Your conditional formatting rule states:
[highlight pink]When Cell Value = 90 - turn light red[/highlight]
[highlight yellow]When Cell Value > 90 - turn yellow[/highlight]

Your value = 90



Randy

 

[strongm]

>Your value = 90


No, it doesn't. That's the point Skip is making.

It displays 90, but the actual value is 89.9991. And it is the actual value that the conditional formatting works against.

 

[randy700]

If you follow the rest of Skip's suggestions....

You might want to round up in the CF formula criteria

The NEW actual value IS 90.




Randy

 

[zelgar]

Additionally, you have 7 different Conditional Formatting currently going on for cell B23. Based upon most of the formulae, what you want is appears to be what rand700 poseted on 18 Mar 15 13:46. Here's what I'd do
1. Get rid of the problem of using the 9.9999 in your IF statements in Cells C9 - C22

=IF(B14="",0,IF(B14="N/A",10,IF(B14="FAIL",0,10)))

2. Get rid of all but 2 of the Conditional Formatting for the conditions when the Cell Value = 90 and Cell Value > 90 (note: based upon your IF statements, you'll never get a value over 90 since you only have 9 cells and the only possible outputs of the modified IF statement are 0 and 10)

 

[strongm]

That's not my interpretation. Skip is suggesting modifying the CF to deal with this, not the actual value, so instead of using a simple 'Format only cells that contain' rule, we use a 'Use a formula to determine which cells to format' rule.

i.e instead of

When Cell Value = 90 - turn whatever

we have something like

When FIXED(Cell Value, 0) = FIXED(90,0) - turn whatever

The ACTUAL value in the cell remains unchanged.

 

[TJVFree]

Thank you all. adding ROUND to my formula made it work

TCB

 

[SkipVought]

That's what I'm talkin' about: a well rounded solution!