Results 1 to 4 of 4

Thread: IF function with conditional formatting

  1. #1

    IF function with conditional formatting



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

    Where do I begin..sorry first post to site. I am trying to use a sum to base all other criteria off of. I have column A (increment), B (hyphen), C (IF function and conditional formatting) on page 1 and the sum on page 2. The end goal is a very basic goal chart in which will fill column C with a color if the criteria is met in regards to the sum on page 2.

    the base value is: =IF(AND(Sheet2!C30>249,Sheet2!C30<500),"True","") the conditional formatting will highlight the cell red if true.
    the second value is:
    =IF(AND(Sheet2!C30>499,Sheet2!C30<750,),"True","")

    the problem comes in when the sum increases say to 600 the initial value turns false and then only one value is highlighted instead of them stacking and the base remaining highlighted as well. Boy I sure hope someone can understand what I am trying to accomplish. Thank you.

  2. #2
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    767
    Articles
    0
    Excel Version
    2010
    Quote Originally Posted by dmf View Post
    Where do I begin..sorry first post to site. I am trying to use a sum to base all other criteria off of. I have column A (increment), B (hyphen), C (IF function and conditional formatting) on page 1 and the sum on page 2. The end goal is a very basic goal chart in which will fill column C with a color if the criteria is met in regards to the sum on page 2.

    the base value is: =IF(AND(Sheet2!C30>249,Sheet2!C30<500),"True","") the conditional formatting will highlight the cell red if true.
    the second value is:
    =IF(AND(Sheet2!C30>499,Sheet2!C30<750,),"True","")

    the problem comes in when the sum increases say to 600 the initial value turns false and then only one value is highlighted instead of them stacking and the base remaining highlighted as well. Boy I sure hope someone can understand what I am trying to accomplish. Thank you.
    Im a little confused. You haven't said which cell carries the formatting, but lets say its C30. Although you have two expressions one would be sufficient as below, because Cell C30 needs to be red if its value is anywhere between 249 and 750:
    Try this conditional format formula for the cell you want to format:

    =AND(Sheet2!C30>249, Sheet2!C30< 750)

  3. #3
    Sorry I was not specific enough. The sheet2!c30 is a constant changing number that the argument is based on. If you would imagine a thermometer. I have increments of 250 from 250 to 40,000 (end goal). The formula I have work correctly but I need to add something else to them. So when c30= 750 the third row is true and it highlights but the two other rows (250 & 500) are now false, I need these to remain true as c30 increases.

  4. #4
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    767
    Articles
    0
    Excel Version
    2010
    Quote Originally Posted by dmf View Post
    Sorry I was not specific enough. The sheet2!c30 is a constant changing number that the argument is based on. If you would imagine a thermometer. I have increments of 250 from 250 to 40,000 (end goal). The formula I have work correctly but I need to add something else to them. So when c30= 750 the third row is true and it highlights but the two other rows (250 & 500) are now false, I need these to remain true as c30 increases.
    OK Im assuming that you are conditionally formatting two different cells, in which case you need two separate expressions. In this case, because your formulae expressions are mutually exclusive, only one can return TRUE so the other is not going to show red.
    I think that you must post an example spreadsheet, showing example values, and how these should affect the colouring of your formatted cells.

Posting Permissions

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