using min function without calculating 0 as minimum

[chusu]

I have a column with different values like this and so on I want to
calculate =Min(B1:B20) but I do not want to have 0 as minimum value.
I this function not to calculate 0 and tell me 2 as minimum value.
here is the example:
2
6
54
4
0
5
8
87
54
56
12
0

answer here is "0" but i want to have "2" as minimum value.

 

[Ron Coderre]

Here are a couple options:

This one is an ARRAY FORMULA (committed with Ctrl+Shift+Enter, instead of
just Enter):
=MIN(IF(A1:A10,A1:A10))

This longer one is a regular formula:
=MIN(INDEX(A1:A10+(A1:A10=0)*10^99,0))

Adjust range references to suit your situation.

Is that something you can work with?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

 

[Ragdyer]

Try this:

=SMALL(B1:B20,COUNTIF(B1:B20,0)+1)