Results 1 to 13 of 13
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Displaying Zero Values (Or 'Un-displaying') (2000)

    I have a very large worksheet of data (i. e. 10x750) that in some cells contain zeros. When I go to Tools-Options-View and uncheck the Zero Values box, it doesn't seem to have any effect, and, as a result, when I try and find, say, the minimum value of all of the entries in a particular column, intending to ignore the zero values, the function (i. e., min(a1:a750)) seems to accept the zero values, and returns as the minimum a zero value. What I would like the function to do is look at all of the values in the range a1:a750, ignoring the zero values, and then return the minimum value. Any help?
    Thanks,
    Jeff

  2. #2
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Displaying Zero Values (Or 'Un-displaying') (2000)

    Whether or not zero is being displayed, as long as it has been entered in a cell, Excel will recognise it as a value.

    Try =small(a1:a750,2). This will pick up the next smallest value after 0.
    Gre

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Displaying Zero Values (Or 'Un-displaying') (2000)

    Unk,
    Doesn't seem to work for me. Can you give me a spreadsheet example-the spreadsheet I am working on exceeded the file limits for posting here.
    Thanks,
    Jeff

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Displaying Zero Values (Or 'Un-displaying') (2000)

    It works for me. The formula is in A11 in the attached workbook.
    Attached Files Attached Files
    Legare Coleman

  5. #5
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Displaying Zero Values (Or 'Un-displaying') (2000)

    Try this.
    Attached Files Attached Files
    Gre

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Displaying Zero Values (Or 'Un-displaying') (2000)

    Why doesn't it work with the attached?
    Attached Files Attached Files

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Displaying Zero Values (Or 'Un-displaying') (2000)

    It does actually work on your worksheet. The second smallest value is 0 as is the 12th. Use the following :<pre> =SMALL(E1:E35,COUNTIF(E1:E35,0)+1)</pre>

    Andrew C

  8. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Displaying Zero Values (Or 'Un-displaying') (2000)

    Thanks to all-I didn't make myself clear that there were multiple cells with zero values!
    Thanks again and Happy Holidays to everyone!
    Jeff

  9. #9
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Displaying Zero Values (Or 'Un-displaying') (2000)

    Regret I missed out crucial step. You first need to eliminate all the zero values in the column by using COUNTIF. You then set SMALL to pick up COUNTIF+1. See the attached.
    Attached Files Attached Files
    Gre

  10. #10
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Displaying Zero Values (Or 'Un-displaying') (2000)

    jkirk,

    Suppose the range in question is A1:A10. Type the following formula and enter it by typing Ctrl+Shift+Enter instead of just Enter. The minimum will be calculated, disregarding zeros, regardless of how many zeros there are:

    <pre>=MIN(IF(A1:A10<>0,A1:A10,""))
    </pre>


    In the formula bar, it will look like this:

    <pre>{=MIN(IF(A1:A10<>0,A1:A10,""))}
    </pre>


  11. #11
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Displaying Zero Values (Or 'Un-displaying') (2000)

    Jim,
    Perhaps you could provide an example spreadsheet-when I try to do what you direct, I can't seem to get it to work.
    Thanks,
    Jeff

  12. #12
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Displaying Zero Values (Or 'Un-displaying') (2000)

    Jim's array formula is working in the attachment.
    Attached Files Attached Files
    -John ... I float in liquid gardens
    UTC -7DS

  13. #13
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Displaying Zero Values (Or 'Un-displaying') (2000)

    Thanks John, I just got back from Vacation

Posting Permissions

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