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

1. ## 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. ## 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.

3. ## 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. ## Re: Displaying Zero Values (Or 'Un-displaying') (2000)

It works for me. The formula is in A11 in the attached workbook.

Try this.

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

Why doesn't it work with the attached?

7. ## 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. ## 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. ## 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.

10. ## 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. ## 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. ## Re: Displaying Zero Values (Or 'Un-displaying') (2000)

Jim's array formula is working in the attachment.

13. ## 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
•