+ Reply to Thread
Results 1 to 15 of 15

Conditional formatting multiple number values but IGNORING text.

  1. #1
    Registered User
    Join Date
    08-23-2013
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    8

    Conditional formatting multiple number values but IGNORING text.

    Hi All,

    I have a spread sheet where there are multiple rows of price quotes from different suppliers.

    Some suppliers have quoted, some however have not and there is a "#N/A" or "no bid".

    SupplierA SupplierB SupplierC SupplierD
    1.3 No Bid 2.3 No Bid

    How can I get a conditional format to check each row for the lowest numeric bid, highlight it but ignore the text?? What I have tried so far doesn't work.

    =G31=MAX($G$31:$J$31)

    This does not seem to work, I don't know how to exclude the text values from the MAX function.

    Thanks in advance

    - Daz
    Last edited by Dazmeister; 09-06-2013 at 07:23 AM. Reason: formatting correction

  2. #2
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Conditional formatting multiple number values but IGNORING text.

    Hi

    Max ignores the text. No problem there.

    #N/A is the one that gives you the problem. Use iferror function in front of your formulas in cells yhat give such a value..
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  3. #3
    Registered User
    Join Date
    08-23-2013
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Conditional formatting multiple number values but IGNORING text.

    Quote Originally Posted by Fotis1991 View Post
    Hi

    Max ignores the text. No problem there.

    #N/A is the one that gives you the problem. Use iferror function in front of your formulas in cells yhat give such a value..
    Thanks for the help Fotis,

    This still doesn't work - the syntax looks wrong though, could you advise? Does the IFERROR need an "=IF"? Do I refer to the entire range that I am looking for errors in?

    =ISERROR($G$31:$L$31)=G31=MAX($G$31:$L$31)

    Thanks in advance,

    - Daz

  4. #4
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Conditional formatting multiple number values but IGNORING text.

    No. I didn't mean this.

    In range g31:L31 you have these values that you get these using formulas, or else you didn't get a #n/a result.

    In front of these formulas put the IFERROR, not to CF formula.

    In my test sheet CF for the data that you added in your first post works great using YOUR formula.

    In you can not handle this, upload a small sample workbook to show how you can do this.


    To attach a small sample workbook.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic

  5. #5
    Valued Forum Contributor
    Join Date
    09-15-2011
    Location
    Chennai, India
    MS-Off Ver
    Excel 2010
    Posts
    436

    Re: Conditional formatting multiple number values but IGNORING text.

    Please check this mechanism.
    Attached Files Attached Files
    Please click 'Add reputation', if my answer helped you.

  6. #6
    Registered User
    Join Date
    08-23-2013
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Conditional formatting multiple number values but IGNORING text.

    Ramananhrm, thank you very much for your simple solution.

    Does this work also with MIN? If I change the formula to MIN, it highlights both numeric values, and not just the lowest value.


    "=MIN(A2:D2)=A2" doesn't seem to work.

    Thanks in advance,

    - Daz

    *EDIT*

    I have tried this, and it doesnt seem to work. please see attached image.

    screenshot.jpg
    Last edited by Dazmeister; 09-06-2013 at 08:33 AM.

  7. #7
    Valued Forum Contributor
    Join Date
    09-15-2011
    Location
    Chennai, India
    MS-Off Ver
    Excel 2010
    Posts
    436

    Re: Conditional formatting multiple number values but IGNORING text.

    I have used both the conditions.

    Please try this updated file.
    Attached Files Attached Files
    Last edited by ramananhrm; 09-06-2013 at 08:32 AM.

  8. #8
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Conditional formatting multiple number values but IGNORING text.

    @ Dazmeiste

    You don't seem to listen what i say...See the example pls!!

    @ ramananhrm

    Do you really believe that i couldn't prepare a sample sheet and post it?

    Point is to explain to OP what's wrong with HIS formula.

    Both of you,pls see the example to understand what i mean..
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    08-23-2013
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Conditional formatting multiple number values but IGNORING text.

    Quote Originally Posted by ramananhrm View Post
    I have used both the conditions.

    Please try this updated file.
    I have tried the conditions in my main file ramananhrm, but still does not function. Any further suggestions? I appreciate the help.

    Please see attached.

    Untitled1.jpg

    Thanks in advance

    - Daz

  10. #10
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Conditional formatting multiple number values but IGNORING text.

    See post#8 !!!!

  11. #11
    Registered User
    Join Date
    08-23-2013
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Conditional formatting multiple number values but IGNORING text.

    Quote Originally Posted by Fotis1991 View Post
    @ Dazmeiste

    You don't seem to listen what i say...See the example pls!!

    @ ramananhrm

    Do you really believe that i couldn't prepare a sample sheet and post it?

    Point is to explain to OP what's wrong with HIS formula.

    Both of you,pls see the example to understand what i mean..
    Thank you for the sample spreadsheet Fotis.

    Does this mean you are saying it is NOT possible to conditionally format any cell where there is "#N/A"? Does the hashtag make it a problem?

    If you see the screenshot when I tried ramananhrms suggested, nothing is highlighted. His example worked OK, but when I tried it, I could not get it to work.

    Do I need to change all "#N/A" to "No Bid"?

  12. #12
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Conditional formatting multiple number values but IGNORING text.

    I have put this formula in my example in D2

    =INDEX(I1:I4,MATCH(G1,J1:J4,0))

    CF does not works.

    Put an IFERROR in front of the formula..

    =IFERROR(INDEX(I1:I4,MATCH(G1,J1:J4,0)),"")

    ..and CF will work great...

    If you can not handle this pls upload a small sample workbook. Not a picture.

  13. #13
    Valued Forum Contributor
    Join Date
    09-15-2011
    Location
    Chennai, India
    MS-Off Ver
    Excel 2010
    Posts
    436

    Re: Conditional formatting multiple number values but IGNORING text.

    So sorry Fotis1991.

    I understood the error.
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    08-23-2013
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Conditional formatting multiple number values but IGNORING text.

    Quote Originally Posted by Fotis1991 View Post
    I have put this formula in my example in D2

    =INDEX(I1:I4,MATCH(G1,J1:J4,0))

    CF does not works.

    Put an IFERROR in front of the formula..

    =IFERROR(INDEX(I1:I4,MATCH(G1,J1:J4,0)),"")

    ..and CF will work great...

    If you can not handle this pls upload a small sample workbook. Not a picture.
    Yes, I understand now!

    Please see photo.

    Thank you both.

    2.jpg

  15. #15
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Conditional formatting multiple number values but IGNORING text.

    That's nice!

    I am here trying to teach you how to make it. Not to offer just a solution!

    So..

    You are welcome and thanks for the feed back.

    As that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thank you.

+ 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. Conditional formatting with multiple text values
    By woodruff in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-26-2013, 10:23 AM
  2. Conditional Formatting - ignoring null values
    By tommyz03 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-17-2012, 06:52 PM
  3. [SOLVED] Conditional formatting ignoring null values
    By gurj_sandhu in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-25-2012, 06:07 AM
  4. [SOLVED] Conditional Formatting Multiple Text Values
    By Graham Taylor in forum Excel General
    Replies: 3
    Last Post: 06-04-2012, 04:39 PM
  5. Changing Text Format with Formula or Conditional Formatting with multiple values
    By gerodr in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-22-2011, 08:54 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