Conditional formatting and numbers

[DashboardNovice]

I have conditional formatting in C4:C7. Only C5 should be turning red because G5 meets the criteria of <>0 or <>100.

The problem is that for some reason, the other cells in column C are turning red as if the CF formula is evaluating to TRUE. This means that 0.00 in column G is recognized as not being 0 or 100. But how can that be true? Could it be that column G is text? I tested that in column H and column G is not text.

1. I don't see how else 0.00 in column G is causing red to appear in column C.
2. Also, is there any way to simplify my formula in G, so that I don't have to use IFERROR( ) for each argument in SUM( )? The reason I had to use IFERROR( ) is because in my original dataset I was getting errors (because the cells/numbers I referenced inside the VALUE( ) were recognized as text.

 

[ETAF]

you have used
=OR($G4<>0,$G4<>100)

so if
g4 does not = 0
g4 does not = 100

none are = 100
so all true

This means that 0.00 in column G is recognized as not being 0 or 100.

well it does NOT = 100 - so TRUE

what conditions do you want to turn red

=AND($G4<>0,$G4<>100)
would only highlight G5

 

[DashboardNovice]

what conditions do you want to turn red

=AND($G4<>0,$G4<>100)
would only highlight G5

I understand now. Thank you.

 

[ETAF]

you are welcome