+ Reply to Thread
Results 1 to 17 of 17

Conditional Formatting Issue

  1. #1
    Registered User
    Join Date
    05-04-2012
    Location
    Nebraska
    MS-Off Ver
    Excel 2003
    Posts
    70

    Conditional Formatting Issue

    Okay so I have a conditional format that is very simple:
    =C5>=(30*0.85)
    If this is true then the cell is supposed to turn red. Which for the most part works fine.

    This was my original formula within the cell itself:
    =AVERAGE(AI8:BF8)/2

    This also works well for the most part unless there is no numbers within the range yet. It will return a #DIV/0! in that case.

    So I switched to a new formula:
    =IFERROR(AVERAGE(AI7:BF7)/2,"")

    This also works well for the most part except for the same scenario where there are no numbers in the range yet. It is turning the cell red which means that the value should be >=(30*0.85), which it is not. The value should be "" or blank. I even tried using "0" instead and it still turns red. Any ideas?

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Conditional Formatting Issue

    "" is not blank, it's a Null Text String.
    Excel considers Text Strings as > Numbers.

    Try changing
    =IFERROR(AVERAGE(AI7:BF7)/2,"")
    to
    =IFERROR(AVERAGE(AI7:BF7)/2,0)

  3. #3
    Registered User
    Join Date
    05-04-2012
    Location
    Nebraska
    MS-Off Ver
    Excel 2003
    Posts
    70

    Re: Conditional Formatting Issue

    Worked like a charm. Thanks Jonmo1

  4. #4
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365
    Posts
    12,817

    Re: Conditional Formatting Issue

    Quote Originally Posted by Jonmo1 View Post
    Excel considers Text Strings as > Numbers.
    Just to add some info, when XL compares contents of cells it first compares the types before comparing values
    F.I. type(text)=2 and type(number)=1 so ="a">1 is always TRUE as is ="">1
    Withe the TYPE() function you can see the different outcomes

  5. #5
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Conditional Formatting Issue

    Quote Originally Posted by Pepe Le Mokko View Post
    Just to add some info, when XL compares contents of cells it first compares the types before comparing values
    F.I. type(text)=2 and type(number)=1 so ="a">1 is always TRUE as is ="">1
    Withe the TYPE() function you can see the different outcomes
    Interesting, I thought it was related to the ASCII Codes.
    Ascii codes for Letters are larger than the ascii codes for numbers..

    0-9 ascii codes are 48-57
    A-Z ascii codes are 65-90
    a-z ascii codes are 97-122

  6. #6
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365
    Posts
    12,817

    Re: Conditional Formatting Issue

    If you look at ASCII codes from other characters like + or - or * they are lower than those of numbers but XL considers them larger than a number for their type() equals 2.
    This being said, this has nothing to do with the thread - Sorry for hijacking

  7. #7
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Conditional Formatting Issue

    I wouldn't say it has nothing to do with the thread..
    It's directly related to 'Why' the original posted formula didn't work.
    'Why' is just as important as 'How to resolve'

    But anyway, you're right. Question asked, and sufficiently answered.

    Glad to help, Caedmonball. Thanks for the feedback.

  8. #8
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Conditional Formatting Issue

    I've always wondered this myself. It has been discussed in other Excel forums but no diffinitive answer was offered.

    I just posted this question in the MS Excel MVP forum. Let's see if anyone there knows.

    My guess was that text is stored in higher bits than numbers (don't laugh!).
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  9. #9
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Conditional Formatting Issue

    Quote Originally Posted by Tony Valko View Post
    I just posted this question in the MS Excel MVP forum. Let's see if anyone there knows.
    Once again, no definitive answer. Some speculation and theories.

    You can see how Excel evalautes the value of different data types by sorting the data.

    For example, enter this data in A1:A10...

    #N/A
    text
    10
    TRUE
    Adam
    Xray
    #VALUE!
    -10
    FALSE
    =""

    Then sort that data in ascending order. You'll get:

    -10
    10
    =""
    Adam
    text
    Xray
    FALSE
    TRUE
    #N/A
    #VALUE!

  10. #10
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Conditional Formatting Issue

    Tony, that seems to support the Type Theory..

    Put =TYPE(A1) next to each of those values.
    Please Login or Register  to view this content.
    Last edited by Jonmo1; 05-24-2013 at 09:00 AM.

  11. #11
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365
    Posts
    12,817

    Re: Conditional Formatting Issue

    I don't think it's speculation, it's a fact, but I can't get my hands on my source

  12. #12
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Conditional Formatting Issue

    Quote Originally Posted by Jonmo1 View Post
    Tony, that seems to support the Type Theory..
    Yeah, but which came first?

    Was the data type value already established then the TYPE(...) function was created. Or, is it vice versa or possibly none of the above?

    I would lean towards the data type value having already been established.

  13. #13
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Conditional Formatting Issue

    Quote Originally Posted by Pepe Le Mokko View Post
    ... I can't get my hands on my source
    That's what I was hoping for when I posted this question in the MS MVP forum but no luck. We do get responses from the folks at MS but not this time. That tells me they didn't even know (or, at least no one was available at the time that knows).

  14. #14
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Conditional Formatting Issue

    Either way, I think we have solution to
    Why Text > Number = TRUE

    I used to think it was related to the ascii codes, but the Type seems to make much more sense.

    Thank you Pepe And Tony (and Caedmonball for starting the thread)

  15. #15
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Conditional Formatting Issue

    Maybe this applies,

    Check the section on default sort order rules.
    http://support.microsoft.com/kb/322067

  16. #16
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Conditional Formatting Issue

    I think the sort operation just proves or demonstrates that one data type has a higher/lower value than another data type but the sort operation doesn't answer the question as to why one data type has a higher/lower value than another data type.

    What I want to know is what logic led the Excel developers to establish this data type value order?

    I doubt if I'll/we'll ever find out!

  17. #17
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Conditional Formatting Issue

    Given "Sort Order" and and "Higher or Lower Value"
    Since Ascending is also referred to as "Lowest to Highest" or "Smallest to Largest" - This indacates values.
    Then I think both things are based on the same set of rules.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1