Return the min >0 for set of non-contiguous numbers?

[mollyway]

I am trying to write a formula that returns the minimum value that is greater than zero for a set of numbers.

Normally, I would just enter in an array such as: MIN(IF(A1:G1>0, A1:G1, FALSE))

The only problem is, my numbers are not contiguous (i.e. their positions are A1, G1, P1, etc.). Does anyone know a formula that will return the minimum, positive number from a set of non-contiguous numbers?

**extra gratitude to anyone who can do this without an array, as the arrays would make my file size quite large.**

Thanks!

 

[barry houdini]

Welcome to the board, mollyway

Is there any pattern to your non-contiguous numbers, e.g. every 10th cell. I presume that there isn't if your example is correct, A1, G1, P1.....

How many cells are we talking about?

What's in B1:F1, H1:O1 etc.?

 

[sgremmo]

I write only because I'm very curious of your ask.

Only little help that i give you it to consider that you could use your formula A1:G1 where every cell of range is linked to the discontinuos cells (i.e. a1=+p1; a2=m4; a3=b2, etc.).

Meanwhile that aspects one formula...

Simone

 

[mollyway]

Hi Barry,

Unfortunately, there is not a pattern to the numbers except that they will all occur on the same row. The number of cells to include in this analysis varies from 2 to 13 (I am running a large number of different scenarios which is why the size of the data set varies).

The suggestion that sgremmo had would work (i.e. create a separate range of contiguous cells that reference my non-contiguous cells), although this would be a somewhat "clunky" solution, and I am always in search of elegance!

I was hoping that there might be an easy solution I hadn't thought of, but perhaps I should move forward with a work around. Thoughts?

 

[mortgageman]

Not fully tested, but the following UDF should work as long as NOT all the numbers are less than or equal to zero. If they all are, it will return 9999999999. (If one of your numbers happens to BE 9999999999, then find someone who actually knows what s/he is doing)

Example Syntax: nzmin(a1,g1,p1)


Public Function nzmin(ParamArray args() As Variant) As Variant
Dim i As Variant
Dim large As Double
large = 9999999999#
For i = 0 To UBound(args)
If args(i).Value > 0 Then
large = Application.WorksheetFunction.Min(large, args(i).Value)
End If
Next 'i
nzmin = large
End Function

Gene, "The Mortgage Man", Klein

Edited for dumb errors

 

[Aladin Akyurek]

If you download and install the morefunc.xll add-in, you can invoke;

=MIN(IF(SETV(ARRAY.JOIN(A1,G1,P1,U1:Z1))>0,GETV()))

which must be confirmed with control+shift+enter, not just with enter.

 

[pgc01]

Hi mollyway
Welcome to the board

Here is one example for 5 non-contiguous cells

=SMALL((A1,C1,E1,G1,I1,K1),1+FREQUENCY((A1,C1,E1,G1,I1,K1),0))

No problem if some of the values are negative.

This assumes that at least one cell is positive (or else add the test).

Hope this helps
PGC

 

[mollyway]

pgc01,

Your solution worked perfectly! Thank you so very much for your help and for making my first posted question such a positive experience.

Take care,
Molly

 

[pgc01]

Hi Molly

I'm glad it worked.

As a remark, you could use a formula like the first formula you posted

=MIN(IF(A1:G1>0, A1:G1, FALSE))

but, since the cells are non-contiguous, you have to convert the group of cells you want to test into an array. And that involves explicitly the number of cells.

Using your formula for a group of 6 cells:

=MIN(IF(CHOOSE(ROW(1:6),A1,C1,E1,G1,I1,K1)>0,CHOOSE(ROW(1:6),A1,C1,E1,G1,I1,K1)))

Confirmed with CTRL+SHIFT+ENTER.

As you see, it has the disadvantage of having the 6, the number of cells. If tomorrow you add 1 or 2 cells the formula will not give you the correct result unless you remember to update the number of cells.

On the other hand it has also an advantage, it will never issue an error. If all the cells are 0 the result is 0.

Kind regards
PGC