+ Reply to Thread
Results 1 to 13 of 13

no display 0

  1. #1
    Forum Contributor
    Join Date
    10-31-2018
    Location
    venezia
    MS-Off Ver
    2007
    Posts
    481

    no display 0

    Hi to all,
    this formula:
    Please Login or Register  to view this content.
    find the values but if it does not find values, it displays 0,
    How to not see 0?
    thank you
    john

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: no display 0

    how about adding an IF in the front, like ... =IF(IFERROR(INDEX($A$2:$A$15,MATCH(0,INDEX(COUNTIF(B$1:B1,$A$2:$A$15&""),),0)),"")=0,"")
    Another option I have found is that you can format the end result so that using conditional formatting >> new rule >> format only cells that contain >> make cell value equal to and put 0 in it then go to format and format as font color and pick white or whatever background color your cell has so it disappears.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Forum Contributor
    Join Date
    10-31-2018
    Location
    venezia
    MS-Off Ver
    2007
    Posts
    481

    Re: no display 0

    Hi sambokid
    the new formula not work result FALSE

  4. #4
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: no display 0

    what are you trying to do with the formula? It looks to me like it returns the first value it finds from A2 to A15 and if that value is a 0 it returns the 0. Without changing your formula and there are no values in A2:A15 it returns blank for me. So the only reason I see it returning a 0 is because the first value it finds in that range is a 0 (like if A2 and A3 are blank and A4 has 0 and A5 has 12, it returns the 0). Have you tried the option using conditional formatting to make the 0 disappear?

    this seems to have removed the zero ... =IF(IFERROR(INDEX($A$2:$A$15,MATCH(0,INDEX(COUNTIF(B$1:B1,$A$2:$A$15&""),),0)),"")=0,"",IFERROR(INDEX($A$2:$A$15,MATCH(0,INDEX(COUNTIF(B$1:B1,$A$2:$A$15&""),),0)),""))

    However, if the first value it finds in A2:A15 is a 0 (like in A4) and there is a value greater than that, say in A5 that will simply return a blank and not the value which as best as I can tell is what your formula would've done anyway.

  5. #5
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: no display 0

    By the way, the other recommendation I could give is that if any of the results in A2 through A15 are being generated by a formula you might want to change the formula so that it returns blank instead of a zero then your formula might work fine, just a thought.

  6. #6
    Forum Contributor
    Join Date
    10-31-2018
    Location
    venezia
    MS-Off Ver
    2007
    Posts
    481

    Re: no display 0

    O.k. sambo kid when work well,
    thank you
    Another problem in workbook attached sheet grafico why in the coolonna C if B is empty is number 682?
    john
    Attached Files Attached Files

  7. #7
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: no display 0

    If I understand your intention, and you are trying to return unique values and ignore zeros you may try this array formula.

    ***Array formula. must use ctrl+shift+enter key combination.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    v A B
    1
    2 10 10
    3 21 21
    4 0 45
    5 45 321
    6 321 4
    7 0 32
    8 4
    9 4
    10 32
    Last edited by AlKey; 06-18-2019 at 05:04 PM.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  8. #8
    Forum Contributor
    Join Date
    10-31-2018
    Location
    venezia
    MS-Off Ver
    2007
    Posts
    481

    Re: no display 0

    Thankyou alkey your formula work well.
    john

  9. #9
    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,375

    Re: no display 0

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    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.

  10. #10
    Forum Contributor
    Join Date
    10-31-2018
    Location
    venezia
    MS-Off Ver
    2007
    Posts
    481
    A help for post #6?

  11. #11
    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,375

    Re: no display 0

    Try this:

    =IF(B2="","",IF(COUNTIF(Input!$D$6:$D$690,B2)=0,"",COUNTIF(Input!$D$6:$D$690,B2)))

  12. #12
    Valued Forum Contributor
    Join Date
    12-14-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2007
    Posts
    439

    Re: no display 0

    Quote Originally Posted by john_cash View Post
    A help for post #6?
    Your formula will do countif two times. If your data is large, this will cause Excel slowly.
    I recommend using this formula instead of your formula.
    Please Login or Register  to view this content.

  13. #13
    Forum Contributor
    Join Date
    10-31-2018
    Location
    venezia
    MS-Off Ver
    2007
    Posts
    481

    Re: no display 0

    Thank you huutang_bd and aligw
    Your formula work well.
    john

+ 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] Display occurrences of text and then display it on search page
    By murray83 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 01-14-2017, 05:21 AM
  2. Replies: 2
    Last Post: 07-25-2016, 01:27 AM
  3. Replies: 2
    Last Post: 08-29-2015, 10:58 AM
  4. Replies: 1
    Last Post: 01-05-2014, 06:19 PM
  5. Find and display the last occurance in two columns and display related cell
    By willia97 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-28-2012, 08:14 PM
  6. Replies: 2
    Last Post: 06-19-2012, 07:48 AM
  7. Replies: 0
    Last Post: 05-15-2012, 10:04 PM

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