+ Reply to Thread
Results 1 to 9 of 9

Conditional Formatting: formula results "" counting as greater than x

  1. #1
    Registered User
    Join Date
    07-31-2017
    Location
    Chicago, IL
    MS-Off Ver
    2010
    Posts
    6

    Conditional Formatting: formula results "" counting as greater than x

    Hi,
    I want to conditional format to highlight values greater than x (in this case 1:59). My formula intentionally blanks out certain rows which should be disregarded (""). These "" cells are being highlighted. It seems the "" cells are counted as infinitely high numbers ("less than" anything does not highlight them). How can I highlight only the non-blank cells that are greater than a given value?
    Thank you!
    (p.s. please disregard those columns to the left, I have a garbage data source and have to use cleaner columns)
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,412

    Re: Conditional Formatting: formula results "" counting as greater than x

    Use a formula rule for J2:

    =AND(J2<>"",J2>0.0826388888888889)
    Attached Files Attached Files
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    07-31-2017
    Location
    Chicago, IL
    MS-Off Ver
    2010
    Posts
    6

    Re: Conditional Formatting: formula results "" counting as greater than x

    Works, thank you!

  4. #4
    Registered User
    Join Date
    07-31-2017
    Location
    Chicago, IL
    MS-Off Ver
    2010
    Posts
    6

    Re: Conditional Formatting: formula results "" counting as greater than x

    I'm curious why excel treats the blank value as a high number...

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,412

    Re: Conditional Formatting: formula results "" counting as greater than x

    It treats them in all sorts of ways, which is why you need to tell Excel to ignore them where appropriate.

    If you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

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

    Re: Conditional Formatting: formula results "" counting as greater than x

    Quote Originally Posted by lennywatson View Post
    I'm curious why excel treats the blank value as a high number...
    When XL compares strings it first evaluates the TYPE() value of each of these strings. For numbers, this is 1, for text, 2, etc..
    "" is a null text string and its TYPE() is therefore 2, so 5>"" is FALSE because type(5) is 1 and type("") is 2. (and of course 1 is smaller than 2)
    Try entering '5 in a cell and 5 in another and check if they are equal...
    If both TYPE() values are the same, than normal comparison rules will apply.

  7. #7
    Registered User
    Join Date
    07-31-2017
    Location
    Chicago, IL
    MS-Off Ver
    2010
    Posts
    6

    Re: Conditional Formatting: formula results "" counting as greater than x

    Thank you!
    What you say makes sense, but I think i'm still missing a piece: 5>"" is False makes sense. But (i've been experimenting to verify) 5<"" returns TRUE (as does 5<"abc"). So...number values are defined as less than text values?

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,412

    Re: Conditional Formatting: formula results "" counting as greater than x

    If you sort a column containing some individual letters and numbers, Excel will put the numbers first. This gives us a clue.

  9. #9
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Conditional Formatting: formula results "" counting as greater than x

    Quote Originally Posted by lennywatson View Post
    Thank you!
    What you say makes sense, but I think i'm still missing a piece: 5>"" is False makes sense. But (i've been experimenting to verify) 5<"" returns TRUE (as does 5<"abc"). So...number values are defined as less than text values?
    Again, you first have to look at the result of the TYPE() function. As TYPE("abc")=2 and TYPE(5)=1, XL stops thinking there and returns TRUE as 2>1.
    See https://support.microsoft.com/en-us/...5-ffa892995899 for more information on the TYPE() function.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] conditional formatting - highlighting all cells greater than "x"
    By random22223 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-18-2019, 03:54 PM
  2. Replies: 7
    Last Post: 10-21-2019, 03:34 PM
  3. Replies: 7
    Last Post: 11-08-2017, 01:31 PM
  4. [SOLVED] IF Formula not working - need to return "NO" if cell is 1% greater or "YES" if less 1%
    By maryren in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-26-2013, 11:34 AM
  5. [SOLVED] Cell Formula to count time with "Greater Than or Equal to" and "Less Than"
    By chriswhite1982 in forum Excel General
    Replies: 3
    Last Post: 06-16-2013, 12:30 AM
  6. [SOLVED] VBA coping conditional formatting " results" to new worksheet.
    By TERRI LEE in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-28-2013, 05:48 AM
  7. [SOLVED] Conditional formatting - formula if cell does NOT contain "," or "@"
    By Armitage2k in forum Excel General
    Replies: 12
    Last Post: 12-02-2012, 06:23 AM

Tags for this Thread

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