PDA

View Full Version : [SOLVED] Finding non-zero minimum in a dynamic array



Rampage123
02-24-2014, 02:48 AM
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
02-24-2014, 02:51 AM
Try this array formula

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

Rampage123
02-24-2014, 02:57 AM
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
02-24-2014, 03:10 AM
Did you array-enter it, Control-Shift-Enter?

Rampage123
02-24-2014, 03:11 AM
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
02-24-2014, 03:43 AM
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
02-24-2014, 08:45 AM
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+