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

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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

Board Regular
Joined
Sep 1, 2004
Messages
55
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

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

Well-known Member
Joined
Jun 30, 2005
Messages
2,015

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884

ADVERTISEMENT

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
 

mollyway

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

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
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
 

Forum statistics

Threads
1,136,928
Messages
5,678,623
Members
419,776
Latest member
mikelowski

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
Top