Results 1 to 4 of 4
  1. #1
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Conditional Formating with Percentages (Excel 2007)

    I'm trying to better understand what Excel is doing when formatting with Icon sets. In the attached worksheet I am comparing using the Percent type with the Number type when configuring the conditional formatting.
    Using Number clearly allows you to specify the numeric ranges that will be formatted.
    On the other hand using the Percent type causes the icons to be applied based on something closer to the Percentranking results.

    Does another lounger have a better way of explaining this?
    Attached Files Attached Files
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Conditional Formating with Percentages (Excel 2007)

    Hi Catharine,
    I believe that what the percentage measures is the percentage movement from the minimum to the maximum - see attached where I have revised your percentrank formulas. If you change B2 to 9 you should see the change in B8 as the percentage tips above 67%.
    Attached Files Attached Files
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Conditional Formating with Percentages (Excel 2007)

    Hi Rory
    Thanks for looking at this
    minor quibble(except your formula doesn't work if the information is not ranked in ascending order and the conditional formatting doesn't care about the sorting)
    Initially when I was playing with this I wondered if there was a bug (in fact there are posts out there saying this is buggy).
    Because I expected the conditional formatting with percentage to appear the way that formatting with numbers does.
    So obviously my understanding of what they mean be formatting with percentages is incorrect.
    I find the help description for this to be extremely unhelpful
    "From Help:
    Use a percentage when you want to visualize all values proportionally because the distribution of values is proportional."

    I know I'm going to be asked about this in classes - so I want to make sure I understand it myself. How would you explain it to someone?

    Catharine
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Conditional Formating with Percentages (Excel

    It's a percentage scale with your minimum value as 0 and the maximum as 100 and everything else in between.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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