Min() wich ignore zero value

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Gilles P (FR) - 06 Feb 2008 15:39 GMT

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)

Reply to this Message

Pete_UK - 06 Feb 2008 15:46 GMT

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

On Feb 6, 3:39 pm, Gilles P (FR) <Gilles...@discussions.microsoft.com>
wrote:

> Hey,
>
[quoted text clipped - 15 lines]
>
> Giles PROVOST(FR)

Reply to this Message

Tyro - 06 Feb 2008 22:38 GMT

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

> Hey,
>
[quoted text clipped - 16 lines]
>
> Giles PROVOST(FR)

Reply to this Message

Tyro - 06 Feb 2008 22:42 GMT

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

> Hey,
>
[quoted text clipped - 16 lines]
>
> Giles PROVOST(FR)

Reply to this Message