1

How do I stop a cell from changing color when it has a value of "<1%". This is the formula in the cell, =IF(C$19=0%,0%,IF(C$19<0.99%,"<1%",ROUNDDOWN(C$19,2))).

I have conditional formatting in the cell; (Rule#1 A1>=80% - Show orange, Rule#2 A1>=95% show Red)

Improve this question
5
  • Sorry, I don't understand the problem. If you have conditional formatting for ">80%", then a cell with "<1%" will not be colored. Is that what you want? What do you mean by "stop from changing color"?
    – sleske
    Mar 16, 2017 at 22:05
  • Yes, I agree the cell shouldn't be colored, but for some reason that I can't seem to figure out it is. The cell shows the color of rule# 2 (red). @Sir Adelaide
    – I Joseph
    Mar 16, 2017 at 22:20
  • OK. Well, from what you write it should not be colored - so there must be something wrong. I think you'll have to provide your Excel file (or a simplified version) showing the problem - I don't see how we can help otherwise.
    – sleske
    Mar 16, 2017 at 22:21
  • Maybe the cell is simply set to a color. It keeps that color unless a rule changes it.
    – Aganju
    Mar 16, 2017 at 22:40
  • 3
    It is colored red because, the way Excel does comparisons, any string will be greater than a number. "<1%" is a string, not a number, so it will always be greater than 95%. You can either set a specific format for that string, as described by @yass, or you can use custom formatting to store the actual numeric value, but display it as you want.
    – Ron Rosenfeld
    Mar 17, 2017 at 1:12

1 Answer 1

Reset to default
3

Create a Rule for "<1%" and use no color for it
Enter Manage Rules and at the same Row of this rule Click Stop if True

Improve this answer
2
  • Thank You @Yass. It took a little massaging but I was able to get it.
    – I Joseph
    Mar 17, 2017 at 2:32
  • Sure because you need to treat "<1%" as string that is why I wrote it between "
    – yass
    Mar 17, 2017 at 8:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.