Minimum formula help.

[de8212]

This has to be easy (for most of the members here) but I can't figure it out.

I have a few columns with various #'s in them but occasionally there will be a zero. I want a minimum formula that will not display any zeros.

Any help is appreciated.

 

[barry houdini]

You can use an array formula like this

=MIN(IF(A1:C100<>0,A1:C100))

confirmed with CTRL+SHIFT+ENTER

 

[de8212]

You can use an array formula like this

=MIN(IF(A1:C100<>0,A1:C100))

confirmed with CTRL+SHIFT+ENTER

Thanks. What does CTRL+SHIFT+ENTER do that Enter doesn't?


I got it to work with your help but the actual formula appears as

{=MIN(IF(A1:C100<>0,A1:C100))}

I guess excel auto corrected it or something?????

 

[T. Valko]

This has to be easy (for most of the members here) but I can't figure it out.

I have a few columns with various #'s in them but occasionally there will be a zero. I want a minimum formula that will not display any zeros.

Any help is appreciated.

Here's another one assuming there won't be any negative numbers.

Book1
	A	B	C
1	74	67	23
2	18	33	82
3	32	0	29
4	10	63	31
5	12	65	0
Sheet1

=SMALL(A1:C5,COUNTIF(A1:C5,0)+1)

Returns 10

 

[Matty]

=SMALL(A1:C5,COUNTIF(A1:C5,0)+1)

Neat alternative, Biff. I like it.

Matty

 

[T. Valko]

Neat alternative, Biff. I like it.

Matty

Thanks!

Make sure you add it to your library.

 

[Matty]

Make sure you add it to your library.

Already done...