Finding non-zero minimum in a dynamic array

[Rampage123]

Hi all,

I am looking for a formula to pick up the non-zero minimum in an array.

The problem is the array is dynamic.

If you look at the attached sheet, I essentially need to find out non-zero minimum stock of an SKU for each depot.

I have put in both the input format in which I have data and the output format I need. Also, please note that I can't change the structure of the array. It has to be in the same tabular format.

Thanks for the help in advance.

[Bob Phillips]

Try this array formula

=MIN(IF(($I$4:$I$15=I4)*($K$4:$K$15>0),$K$4:$K$15))

[Rampage123]

Hi xld,

Thanks for the quick formula. But it is still picking zero. I need to find the non-zero minimum.


I think the $K$4:$K$15 you have put in the value_if_true part of IF function is looking at entire array across all depots. I need the non-zero minimum in the particular depot only.


Regards.

[Bob Phillips]

Did you array-enter it, Control-Shift-Enter?

[Rampage123]

xld,

Thanks for the help. The formula is working fine as array formula. I wrote it as a normal formula earlier.

Now, I wanted to know if I can write this formula in a macro. I am unsure about array formula in macros.

Regards,
Aravind

[Bob Phillips]

    Range("L4").FormulaArray = "=MIN(IF(($I$4:$I$15=I4)*($K$4:$K$15>0),$K$4:$K$15))"
    Range("L4").AutoFill Range("L4:L15")

[lecxe]

Hi

Since your numbers are positive, this is another option, non array-entered:

=1/AGGREGATE(14,6,1/($I$4:$I$15=I4)/$K$4:$K$15,1)

Remark: only for xl2010+