Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Conditional Formatting Problem

    Loungers, I have the formula (below) in a cell

    =IF(ISERROR(TEXT(K30/L30,"0%")),"",TEXT(K30/L30,"0%"))

    I want the conditional formatting to change if the value returned is less than 100%.

    However, there seems to be an issue where if I use less than 100% the conditional formatting is not trigered, I guess it has somthing to do with the text function.

    If any one out there has any thoughts, they would be much appreciated.

    Thanks

  2. #2
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    The value returned in the cell you refer to is a text value, so you cannot compare it to a number in the conditional formatting statement.

    As you know the underlying cells, which contain numbers, you should get the result you want with the following in your conditional formatting formula:

    =IF(ISERROR(K30/L30),0,((K30/L30)<1))

    This will return:

    TRUE of there is a valid percentage which is less than 100%

    FALSE if either the percentage is invalid (eg L30=0) or if the percentage is 100% or more

    We'd need to know more about the error conditions you are expecting to get the first test right - it could be that you are testing for absent values rather than L30=0 ?

  3. #3
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Martin - thanks for the help.

    Will try it out tomorrow and see how it it works.

    Thankyou for your assistance.

    Will post back the results

  4. #4
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    MartinM - works just right.

    Will see how it handles othe error conditions - but look Ok for what I need at this stage

    Cheers

Posting Permissions

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