Results 1 to 13 of 13

20011221, 09:43 #1
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Displaying Zero Values (Or 'Undisplaying') (2000)
I have a very large worksheet of data (i. e. 10x750) that in some cells contain zeros. When I go to ToolsOptionsView 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

20011221, 12:48 #2
 Join Date
 Nov 2001
 Location
 Vienna, Wien, Austria
 Posts
 5,009
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Displaying Zero Values (Or 'Undisplaying') (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.Grüße

20011221, 13:37 #3
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Displaying Zero Values (Or 'Undisplaying') (2000)
Unk,
Doesn't seem to work for me. Can you give me a spreadsheet examplethe spreadsheet I am working on exceeded the file limits for posting here.
Thanks,
Jeff

20011221, 14:37 #4
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Displaying Zero Values (Or 'Undisplaying') (2000)
It works for me. The formula is in A11 in the attached workbook.
Legare Coleman

20011221, 15:03 #5
 Join Date
 Nov 2001
 Location
 Vienna, Wien, Austria
 Posts
 5,009
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Displaying Zero Values (Or 'Undisplaying') (2000)
Try this.
Grüße

20011221, 15:09 #6
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Displaying Zero Values (Or 'Undisplaying') (2000)
Why doesn't it work with the attached?

20011221, 15:43 #7
 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 'Undisplaying') (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

20011221, 15:47 #8
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Displaying Zero Values (Or 'Undisplaying') (2000)
Thanks to allI didn't make myself clear that there were multiple cells with zero values!
Thanks again and Happy Holidays to everyone!
Jeff

20011224, 10:32 #9
 Join Date
 Nov 2001
 Location
 Vienna, Wien, Austria
 Posts
 5,009
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Displaying Zero Values (Or 'Undisplaying') (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.
Grüße

20011227, 23:41 #10
 Join Date
 Mar 2001
 Location
 Silicon Valley, California, USA
 Posts
 273
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Displaying Zero Values (Or 'Undisplaying') (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>

20011231, 08:26 #11
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Displaying Zero Values (Or 'Undisplaying') (2000)
Jim,
Perhaps you could provide an example spreadsheetwhen I try to do what you direct, I can't seem to get it to work.
Thanks,
Jeff

20011231, 17:45 #12
 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 'Undisplaying') (2000)
Jim's array formula is working in the attachment.
John ... I float in liquid gardens
UTC 7±DS

20020102, 18:55 #13
 Join Date
 Mar 2001
 Location
 Silicon Valley, California, USA
 Posts
 273
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Displaying Zero Values (Or 'Undisplaying') (2000)
Thanks John, I just got back from Vacation