Results 1 to 6 of 6

Thread: Conditional formatting

  1. #1

    Conditional formatting



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Dear everybody,

    I need some inspiration for a conditional formatting:

    Today I have a "Controlchart" that must be written once an hour. In the top rows, there are the conditions: e.g. the values must be 100 and not larger than 110 and not lower than 90. In the following rows where the values are written, there is a conditional formatting telling that the values become red if they are outside the desired are (i.e. between 90 and 110).

    NOW I would like to add a tendency formatting, i.e. even though the values are in the specificed area (i.e. between 90 and 110, but optimal 100), if the last, say 7 inputs are all in the lower area (i.e. between 90 and 100) they should become red, despite they are withtin the specifications above.

    I hope it makes sense, otherwise please let me know.

    All the best,
    DID

  2. #2
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    767
    Articles
    0
    Excel Version
    2010
    It would help if you can post an example showing how the data is updated, and which cells are to be formatted red.

  3. #3
    OK, here is an examples of what I am thinking of.

    In this file, I have a conditional formatting of the values inserted in B8 and onwards. The conditions are defined by an upper and lower range in C3-5.

    Now as you can see, the values written in B8 become red when the values are outside the range. Now the last six values are all below the target (100) and now I would like a formatting saying that if more than 6 values are all below the target (in this case 100) the next value should become red if it is below 100, even though it is more than 90 (i.e. within the limits).


    By this I am able to see a tendency upon which I can react, when it is too low.


    I really hope it makes sense, and that this can be made.

    All the best.
    Attached Files Attached Files

  4. #4
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    767
    Articles
    0
    Excel Version
    2010
    Hi
    Ive modified your example. Im using COUNTIF in helper column C to keep track of the number of consecutive values below 100. When this is equal to 7, an additional format comes into play in Col B on the same row.
    Does that help?
    Attached Files Attached Files

  5. #5
    Quote Originally Posted by Hercules1946 View Post
    Hi
    Ive modified your example. Im using COUNTIF in helper column C to keep track of the number of consecutive values below 100. When this is equal to 7, an additional format comes into play in Col B on the same row.
    Does that help?
    You guys are so f-ing clever!

    Thanks from all my heart....

  6. #6
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    767
    Articles
    0
    Excel Version
    2010
    Glad to be of help and thanhks for the feedback


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •