+ Reply to Thread
Results 1 to 6 of 6

Multiple criteria for Conditional Formatiing with numbers and text

  1. #1
    Registered User
    Join Date
    01-18-2022
    Location
    Australia
    MS-Off Ver
    10
    Posts
    3

    Question Multiple criteria for Conditional Formatiing with numbers and text

    I have a lot of data from laboratory results. I get the results with some values like '<0.0005', 0.0001, 0.008, etc. I have a limit I need to test these values against e.g. I want to highlight the cells that have a value >0.5. When I do this, it will also highlight the cells with values of <0.0005. A fix could be changing the <0.0005 to 0 but I don't want that as it is technically wrong. I want a formula that says "if cell is >0.5, and does not contain any text (the symbol "<"), then highlight red". Any suggestions?

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Multiple criteria for Conditional Formatiing with numbers and text

    Maybe something like.....
    =AND(M8>0.5,LEFT(M8,1)<>"<")
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    01-18-2022
    Location
    Australia
    MS-Off Ver
    10
    Posts
    3

    Re: Multiple criteria for Conditional Formatiing with numbers and text

    Hi! Thank you, it seems to work but not consistently. Would you mind explaining it a bit so I can understand what I'm doing wrong?

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: Multiple criteria for Conditional Formatiing with numbers and text

    Ford's formula could be read to say, "highlight red providing the value in M8 is greater than five tenths AND the left most character in M8 isn't a less than symbol".
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Multiple criteria for Conditional Formatiing with numbers and text

    Quote Originally Posted by JeteMc View Post
    Ford's formula could be read to say, "highlight red providing the value in M8 is greater than five tenths AND the left most character in M8 isn't a less than symbol".
    Let us know if you have any questions.
    Thanks for the assist

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: Multiple criteria for Conditional Formatiing with numbers and text

    @Ford, You're very welcome.

+ 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] Summation of Numbers with Text & Numbers in Multiple Cells with Multiple Criteria...
    By e4excel in forum Excel Formulas & Functions
    Replies: 25
    Last Post: 09-14-2017, 01:46 PM
  2. Help with conditional formatiing?
    By abqhusker in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 06:05 PM
  3. [SOLVED] Help with conditional formatiing?
    By abqhusker in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 PM
  4. Help with conditional formatiing?
    By abqhusker in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 PM
  5. [SOLVED] Help with conditional formatiing?
    By abqhusker in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 10:05 AM
  6. Help with conditional formatiing?
    By Martin P in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-06-2005, 07:05 AM
  7. [SOLVED] Help with conditional formatiing?
    By abqhusker in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 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