Solved: VBA- Smallest non-negative number in a range

[nitinbarath]

Hi ... How do i write a function that determines the smallest non-negative number in a given range ?

[Bob Phillips]

=MIN(IF((A2:A20<>"")*(A2:A20>=0),A2:A20))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter.Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually.When editing the formula, it must again be array-entered.

[nitinbarath]

Thanks,,,I tried this, but got an error. Unfortunately, it is a discrete set of numbers and not sequential as in an array. So excel gives me an error stating that I have entered too many arguments. Any suggestions ?

[Bob Phillips]

How is the data laid out then?

[nitinbarath]

4 separate cells, in the same row, but not adjacent to each other

[Bob Phillips]

Yeham but are they a pattern, say every 5th cell, or what?

[nitinbarath]

they skip a cell ... eg b3 d3 f3 h3... b4 d4 f4 g4 and so on.. these are prices that i want to compare and get the minimum price, weeding out '0' which doesn't make sense.. just to give you a perspective....

[Bob Phillips]

Ah good, I thought there would be

=MIN(IF((MOD(COLUMN(B3:H3)-COLUMN(B3),2)=0)*(B3:H3<>"")*(B3:H3>=0),B3:H3))

still an array formula

[nitzbar]

hi.. i tried =MIN(IF((MOD(COLUMN(B3:H3)-COLUMN(B3),2)=0)*(B3:H3<>"")*(B3:H3>=0),B3:H3))
but it still returns '0' sometimes......

[mdmackillop]

A picture can paint a thousand words. Why not post a sample?

[Mikey]

Change >=0 to just > 0, i.e.

=MIN(IF((MOD(COLUMN(B3:H3)-COLUMN(B3),2)=0)*(B3:H3<>"")*(B3:H3>0),B3:H3))

That way you'll only get zero if there are no positive values

[Bob Phillips]

hi.. i tried =MIN(IF((MOD(COLUMN(B3:H3)-COLUMN(B3),2)=0)*(B3:H3<>"")*(B3:H3>=0),B3:H3))
but it still returns '0' sometimes......

That is because 0 is a non-negative number, which is what you asked for.

[nitzbar]

my mistake.. i meant.. positive number.... i changed it accordingly... thank you..