minimum greater than zero

[OkieViking]

I have a table populated with equations. I need to write a function to find
the smallest value in that table. However, I want to ignore the zero values.
From a dataset containing 8, 5, 0, 7 I want to find 5, not zero.

How do you do that?

 

[Don Guillett]

This question seems to be going around today. Homework?

non array enter with enter only
=SMALL(C1:C10,COUNTIF(C1:C10,0)+1)

array - enter with control+shift+enter
=MIN(IF(C1:C10>0,C1:C10))

 

[N Harkawat]

=min(if(a1:a10<>0,a1:a10))
array entered (control+shift+enter)

will find the smallest of all non-zero values

 

[Peo Sjoblom]

One way

=LARGE(A1:A5,COUNTIF(A1:A5,">0"))


Regards,

Peo Sjoblom

 

[OkieViking]

No, not for me anyway. This is for work.

Thanks

 

[OkieViking]

The cells I am looking at are all on one row, but every other column. How do
I make the below work?

 

[Aladin Akyurek]

The cells I am looking at are all on one row, but every other column. How do
I make the below work?

[...]

=MIN(IF((MOD(ROW(A3:A8)-CELL("Row",A3:A8)+0,2)=0)*A3:A8,A3:A8))

which you need to confirm with control+shift+enter instead of the usual
enter.

Adjust the range to suit.

 

[Aladin Akyurek]

Make that...

=MIN(IF((MOD(ROW(A3:A8)-CELL("Row",A3:A8)+0,2)=0)*(A3:A8)>0,A3:A8))

 

[Hecwill]

Hi, I found this formula very helpful, but how can I make it to work in a
conditional formating ?Gives error.

array - enter with control+shift+enter

 

[Biff]

Hi!

Assume you want to conditionally format the range C1:C10.

Select the range C1:C10
Goto Format>Conditional Formatting
Formula is: =C1=MIN(IF(C$1:C$10>0,C$1:C$10))
Click the Format button
Select the desired style(s)
OK out

Biff