Calling function from worksheet with nonconcurrent range

I have a User Defined Function (for finding the next minimum value in a range) that is not working for nonconcurrent ranges when called from within a worksheet. It returns #VALUE!

This works: =nextmin(A11:A17)
This does not work: =nextmin(D13,E13,F13,F16)

However calling the function from within vba works:

Please Login or Register  to view this content.

Here is the function:

Please Login or Register  to view this content.

If interested, here is the subsequent array sorting function I found (with link to author page):

Please Login or Register  to view this content.

Any help is appreciated. Thanks so much.

As written, NEXTMIN is returning a Long data type. A spreadsheet can only take Double.

Please Login or Register  to view this content.

will take care of that.

In general though, it seems that NEXTMIN is doing something that could also be done by the spreadsheet funtions RANK or SMALL.

Using spreadsheet functions (even in fearsome combinations) is usualy faster than using UDFs.

Thanks for your posts.

mikerickson - I tried your suggestion, but it didn't work. I would rather use an excel function too, but couldn't get one to work for what the user wanted from their spreadsheet. They want the next to smallest number from a range of nonconcurrent cells. And, they will be adding more to this spreadsheet...

FrankBoston - I believe you hit the problem right on the nose. If there was a Range Excel function as in vba, adding that to the function call would work.

Thanks again & please add any suggestion if you have them.

So this doesn't work for you?
=SMALL((D13,E13,F13,F16),2)

Just FYI... using:

=nextmin((D13,E13,F13,F16))

gives different results compared to:

=small((D13,E13,F13,F16),2)

1.) "Small" does not count blank cells, whereas "Nextmin" reads them as zero.
2.) "Small" reads each entry as an individual entry, "Nextmin" counts duplicates as one (i.e. if you have: 1,1,2,3,5 - Nextmin returns 2, small returns 1).

This should find the second lowest value in one pass, without going through the sort.

Please Login or Register  to view this content.

It will accept values as well as ranges and reads an empty cell as 0.

FrankBoston - thank you, the extra parenthesis make my function work ;>)

Ikaabod - Ahhh very nice. I did not know of the SMALL function. Works like a champ, thank you.

mikerickson - Thanks for the code, I will definitely try it out.

Thanks again for all of your help. Much appreciated.