none
2007 or 2010 Conditional Fromating

    คำถาม

  • I dare MS Excel SW engineers to explain how the "Stop if True" feature works.  No matter how the rules are ordered it does not seem to matter if the "Stop if True" box is checked or not.  To prove it to me, please create an example so that I can try it on my excel app and show the difference when the "Stop if True" box is checked as compared to when it is not checked.  Please attempt to make the example complete enough so that it worked consistently with the rules you think you have in place based on different ordering of the conditional formatting statements.  Thank you very much.  don.murphy@cubic.com
    10 พฤษภาคม 2555 16:51

คำตอบ

  • If you are setting the same single format feature - in your example,- background color - then the higher priority CF (the one on top) is applied, even if both are TRUE, even if Stop on True is unchecked. That is the "rule of priority", which is how CF has always applied, and how most logical systems are set up.  Your example is inconsistent - do you want it yellow, or do you want it red? As the designer, you want it to be determined exactly, so decide first. If you had set the Font to Bold on the second condition, all would have been well, because you are exactly determining the outcome.

    Note that your formula

     "=if($C$2=3,True,False)"

    could just be

     "=$C$2=3

    So, a more logical system would be

    =C2>=100   Red

    =C2>=50     Yellow

    =C1>=10     Green

    which is easier to apply than

    =C2>=100   Red

    =AND(C2>=50, C2<100)     Yellow

    =(C1>=10, C2<50)     Green

    Which is why, historically, the first TRUE result sets the format.


    HTH, Bernie


    10 พฤษภาคม 2555 20:23

ตอบทั้งหมด

  • I'm up for the "dare" - I hope ;-)

    If you select different ranges, partially overlapping, and apply CF to each of those ranges independently and choose different format attributes for each rule, then how "Stop if True" comes into play is easier to see. Another important thing to note is that CFs are now "additive" rather than "exclusive"

    To show how Stop if True works, select column A and apply a CF that shows all numbers greater than 2 by setting the background to yellow. Then select row 3 and apply a CF that sets the font to Bold for all numbers greater than 6.  In cell A3, enter 8. It should be both yellow _and_ bold.  Hey! That's different - two different formats were applied by CF - you used to be able to only get formats based on one CF level.  If you are trying to set the background based on the two rules, then only one gets applied, of course.

    Select cell A3, and look at the CF. There are two rules, and if you check "Stop if true" to the top rule you will lose the formatting applied by the second.  If you had followed the same steps in XL2003, cell A3 would have only had the CF applied in the second step. And, of course, the formatting applied must not be addressing the same property, or only the higher priority rule will be applied (that is what I expect is the source of your confusion).

    So.....  was I up to your dare?


    HTH, Bernie


    10 พฤษภาคม 2555 18:23
  • Almost.  Thanks for the quick reply.  Very kind of you.

    I tried some testing based on what you said and found some interesting aspects that still make no sense to me.  

    I created  two CF rules that pertain to the exact same range.  The range I selected for each rule was $E$2:$E:35.

    I then selected RULE ONE to be "=if($C$2=3,True,False)" formatted as RED.

    I then selected RULE TWO to be "=if($C$3=3,True,False)" formatted to be YELLOW.  

    RULE ONE appears above RULE TWO in the CF window.  That's what makes it RULE ONE since it appears first.

    One would think that RULE TWO would override RULE ONE so that the range is Yellow unless the "Stop if True" check box is checked for RULE ONE in which case RULE ONE should rule and the range should be Red.  But it does not work that way and therefore is not intuitively obvious.

    For example, if I input 3 into cell C2 and also in cell C3, then the range E2:E35 is always RED no matter how I check the "Stop if True" check boxes.

    In other words, the first Rule listed in the CF window always overrides the other rules for the specified range.  This makes no sense.

    The other interesting aspect is that the formatting does not appear correctly in many cases unless I scroll the screen to refresh it.

    Obviously my intuitive thoughts are not working for me to use this correctly.

    Thoughts?  Suggestions on how I should change my perspective so that I can understand the software rules that were put into place?

    I'll reread your thoughts above and experiment with them a bit tonight to see if I can develop that new perspective I need.

    Until then, let me know what you think about my example above once you play with it for awhile.

    Thanks again,

    Don



    Don Murphy

    10 พฤษภาคม 2555 19:54
  • If you are setting the same single format feature - in your example,- background color - then the higher priority CF (the one on top) is applied, even if both are TRUE, even if Stop on True is unchecked. That is the "rule of priority", which is how CF has always applied, and how most logical systems are set up.  Your example is inconsistent - do you want it yellow, or do you want it red? As the designer, you want it to be determined exactly, so decide first. If you had set the Font to Bold on the second condition, all would have been well, because you are exactly determining the outcome.

    Note that your formula

     "=if($C$2=3,True,False)"

    could just be

     "=$C$2=3

    So, a more logical system would be

    =C2>=100   Red

    =C2>=50     Yellow

    =C1>=10     Green

    which is easier to apply than

    =C2>=100   Red

    =AND(C2>=50, C2<100)     Yellow

    =(C1>=10, C2<50)     Green

    Which is why, historically, the first TRUE result sets the format.


    HTH, Bernie


    10 พฤษภาคม 2555 20:23
  • Thank you so much.

    I was looking for that different perspective that would help me see the light and the example you replied with did it.

    I will share my new insight (which is as you say "is the way it has always been") with the rest of the people at my company that are having problems with CFs.

    I can now use the CF process in ways that are always consistent.  At least so far.  I wish I know of a place I could go to that would have examples on how to interpret the way in which these logic decisions are performed.  I could then point others to that location.  I must admit, without being a software programmer, I would never have selected the logic that was used unless it was explained to me by an expert like you.

    Thanks again for your time and effort and for your patience with me.

    Don


    Don Murphy

    11 พฤษภาคม 2555 17:00