PDA

View Full Version : Solved: VBA- Smallest non-negative number in a range



nitinbarath
08-06-2008, 05:54 AM
Hi ... How do i write a function that determines the smallest non-negative number in a given range ?

Bob Phillips
08-06-2008, 06:03 AM
=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
08-06-2008, 06:30 AM
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
08-06-2008, 06:50 AM
How is the data laid out then?

nitinbarath
08-06-2008, 06:56 AM
4 separate cells, in the same row, but not adjacent to each other

Bob Phillips
08-06-2008, 08:10 AM
Yeham but are they a pattern, say every 5th cell, or what?

nitinbarath
08-06-2008, 08:13 AM
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
08-06-2008, 08:26 AM
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
08-07-2008, 09:32 AM
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
08-07-2008, 02:24 PM
A picture can paint a thousand words. Why not post a sample?

Mikey
08-07-2008, 05:36 PM
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
08-08-2008, 01:36 AM
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
08-08-2008, 01:47 PM
my mistake.. i meant.. positive number.... i changed it accordingly... thank you..