using min function without calculating 0 as minimum

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

chusu - 04 Oct 2007 00:49 GMT

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.

Reply to this Message

Ron Coderre - 04 Oct 2007 01:41 GMT

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)

>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.
[quoted text clipped - 14 lines]
>
> answer here is "0" but i want to have "2" as minimum value.

Reply to this Message

Ragdyer - 04 Oct 2007 05:32 GMT

Try this:

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

Signature

HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

> 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.
[quoted text clipped - 14 lines]
>
> answer here is "0" but i want to have "2" as minimum value.

Reply to this Message