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

mollyway

New Member
Joined
Sep 30, 2006
Messages
3
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!
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
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.?
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
Hi mollyway
Welcome to the board

Here is one example for 5 non-contiguous cells

Code:
=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
 
Upvote 0
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
 
Upvote 0
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:

Code:
=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
 
Upvote 0

Forum statistics

Threads
1,218,845
Messages
6,144,807
Members
450,567
Latest member
Mplz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top