Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Feb 2003
    Location
    Bend, Oregon
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Precision and Conditional Formatting (2002 SP-2)

    I maintain a spreadsheet for the executives of my company which tracks many key performance indicators across all departments, comparing YTD averages to previous years' averages as well as target values. One of the indicators is System Availability, which is measured as a percentage of total hours in the measurement period. The YTD average column is formatted to appear as a 3-decimal percent value (e.g., 99.918%), and uses conditional formatting to change the font color to red if the average falls below the target value of 99.900% (condition 1), and orange (shows up better than yellow) if the cell value falls below 99.910%.

    While the conditional formatting works fine when the value falls below the minimum (99.900%), the problem I'm running into, is that Excel conditional formatting doesn't seem to differentiate the second or third decimal places. Values such as 99.918%, 99.950%, and 99.962% are showing up in orange font, even though they are clearly greater than the 99.910% warning limit.

    Although using a multiplier in the conditional formatting formula to raise the comparative values (multiply the cell and target values by 100000 so that I'm comparing 99918 to 99910 instead of 99.918 to 99.910) is a successful work-a-round, I'd rather correct the underlying problem (whatever that is), if I can. Anyone have an idea of the real cause of this issue and the fix?

    Thanks, and sorry for being so long-winded, but I wanted to be clear about what's going on.

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Ankeny, Iowa, USA
    Posts
    298
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Precision and Conditional Formatting (2002 SP-2)

    This seemed to work fine for me setting condition 1 as
    Cell Value is Less Than .0999

    and condition 2 as
    Cell Value is Between .999 and .9991

    Are these set the same as what you had? I'm on Excel 2000 SP-3, so possibly that makes a difference...

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Precision and Conditional Formatting (2002 SP-2)

    At first look, I thought this was a typical rounding problem. However, the numbers you quoted look like they are too far apart for this to be the case. Could you upload a workbook that has been sanitized of any private data that shows the problem? I think that will save a lot of time guessing what might be going on.
    Legare Coleman

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Precision and Conditional Formatting (2002 SP-2)

    Could this be because you have overlooked that 99.910% is five decimal points (i.e., it's 0.99910) when you set the condition? I occasionally forget this myself when I work with 'nn.nn%' numbers (4 decimal points) and thoughtlessly round to 2 instead of 4 decimal points.
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    Star Lounger
    Join Date
    Feb 2003
    Location
    Bend, Oregon
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Precision and Conditional Formatting (2002 SP-2)

    John,

    Thank you very much for reminding me that, with Excel (and most computer mathematics), what you SEE is NOT, necessarily, what you GET. Especially when working with cells that are formatted as percentages. This was exactly the issue. I should have been adding .0001 to the target value instead of .01 in order to create my "warning" range. Your help has been VERY helpful (and humbling).

  6. #6
    Star Lounger
    Join Date
    Feb 2003
    Location
    Bend, Oregon
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Precision and Conditional Formatting (2002 SP-2)

    Thank you for responding to this issue. The problem was that I forgot that I was working with cells formatted for percentages. See my response to JohnBF for an explanation.

  7. #7
    Star Lounger
    Join Date
    Feb 2003
    Location
    Bend, Oregon
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Precision and Conditional Formatting (2002 SP-2)

    Legare,

    Thank you for responding. In a way, rounding was to blame, but it wasn't an issue with Excel. Rather, the problem was in the way I was looking at the cells containing the percentages. See my response to JohnBF for an explanation. Sometimes, experience and familiarity can work against you.

Posting Permissions

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