Min() wich ignore zero value

[Gilles P (FR)]

Hey,

Is it possible to have a formula with standard Excel function that gives the
Min value without the zero value ?

Ex
Col A
Row1 2
Row2 3
Row3 0
Row4 5

The formula gives for minus of A1:A4 = 2

I'm sorry for this sample question but i don't find a light formula...

Thanks

Giles PROVOST(FR)

 

[Pete_UK]

Try this array* formula:

=MIN(IF(A1:A5>0,A1:A5,10^100))

* as this is an array formula, then once you have typed it in, or
subsequently amend it, you should commit it using CTRL-SHIFT-ENTER
(CSE) instead of the usual ENTER. If you do this correctly, then Excel
will wrap curly braces { } around the formula when viewed in the
formula bar - you should not type these yourself.

Adjust the ranges to suit your data.

Hope this helps.

Pete

 

[Tyro]

You can use this array formula. After typing in the formula press
Ctrl+Shift+Enter, not just Enter.

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

Tyro

 

[Tyro]

If you want to average non-zero, positive values only enter this array
formula + Ctrl+Shift+Enter

=MIN(IF(A1:A4>0,A1:A4))

Tyro