Return minimum POSITVE value from range

[TheRobsterUK]

Hi,

Suppose I have a worksheet with the following data:

(column1)......... (column2)
Savings..................I.D.
-243...................... 01
-205...................... 02
-165.......................03
-87.........................04
57..........................05
109........................06
205........................07
303........................08

What I need to do is look up the smallest -positive- number in the
"Savings" column and the look over to the "I.D." column and retrieve
the corresponding I.D.

So in the above example, look up the value 57 and then retrieve the
corresponding I.D. of 05.

Is there a way to do this? I would have thought an array formula of
some kind but I haven't met with any success so far. In my actual
spreadsheet I have over 10,000 entries in the "Savings" column so any
formula would need to be efficient.

Thanks in advance
-Rob

 

[Guest]

If your first column of data is in A3:A10, and your second column in B3:B10,
I think this should do it.
=VLOOKUP((MIN(IF((A3:A10>0)*(A3:A10),(A3:A10)))),A3:B10,2,0)

This is an array formula and must be entered by pressing Ctrl+Shift+Enter.

This formula will return #N/A if all the values are negative. The formula
below should prevent the error. It's also an array formula.

=IF(ISNA(VLOOKUP((MIN(IF((A3:A10>0)*(A3:A10),(A3:A10)))),A3:B10,2,0)),"All
values are
negative.",VLOOKUP((MIN(IF((A3:A10>0)*(A3:A10),(A3:A10)))),A3:B10,2,0))

 

[Max]

Assuming data in cols A & B

Try something like this in say, C1:

=MIN(IF(A2:A10>0,B2:B10))

Array-enter the formula,
i.e. press CTRL+SHIFT+ENTER
instead of just pressing ENTER

Adapt the ranges to suit ..

 

[Max]

Correction to earlier formula, sorry ..

Try something like this instead in say, C1:

=INDEX(B2:B10,
MATCH(MIN(IF(A2:A10>0,A2:A10)),A2:A10,0))

Array-enter the formula,
i.e. press CTRL+SHIFT+ENTER
instead of just pressing ENTER

Adapt the ranges to suit ..

Note that in the event of any ties in the min value > 0,
the formula will return the 1st matched value from col B
(the one highest up), e.g., if you have say, 2 identical number 57's
in col A, then the value returned from col B will be 5
....
57 5
57 6
....

 

[Bruno Campanini]

"TheRobsterUK" <[email protected]>
wrote in message

Hi,

Suppose I have a worksheet with the following data:

(column1)......... (column2)
Savings..................I.D.
-243...................... 01
-205...................... 02
-165.......................03
-87.........................04
57..........................05
109........................06
205........................07
303........................08

[...]

Another one:

{=IF(COUNTIF(A3:A10,">0"),VLOOKUP(MIN(IF(A3:A10>0,
A3:A10)),A3:B10,2,0),"No positive number")}
FormulaArray

Bruno

 

[R.VENKATARAMAN]

use function <DMIN>
see help
=======

"TheRobsterUK" <[email protected]>
wrote in message

Hi,

Suppose I have a worksheet with the following data:

(column1)......... (column2)
Savings..................I.D.
-243...................... 01
-205...................... 02
-165.......................03
-87.........................04
57..........................05
109........................06
205........................07
303........................08

[...]

Another one:

{=IF(COUNTIF(A3:A10,">0"),VLOOKUP(MIN(IF(A3:A10>0,
A3:A10)),A3:B10,2,0),"No positive number")}
FormulaArray

Bruno

 

[Bruno Campanini]

use function <DMIN>
see help

I don't like to set database, criteria fields etc. in Excel.
I'm prepared to use DMIN() (very, very rarely indeed, as all domain
functions!) in Access, not in Excel.

Ciao
Bruno

 

[Ron Rosenfeld]

Hi,

Suppose I have a worksheet with the following data:

(column1)......... (column2)
Savings..................I.D.
-243...................... 01
-205...................... 02
-165.......................03
-87.........................04
57..........................05
109........................06
205........................07
303........................08

What I need to do is look up the smallest -positive- number in the
"Savings" column and the look over to the "I.D." column and retrieve
the corresponding I.D.

So in the above example, look up the value 57 and then retrieve the
corresponding I.D. of 05.

Is there a way to do this? I would have thought an array formula of
some kind but I haven't met with any success so far. In my actual
spreadsheet I have over 10,000 entries in the "Savings" column so any
formula would need to be efficient.

Thanks in advance
-Rob

Array-enter:

=INDEX(ID,MATCH(MIN(IF(Savings>0,Savings)),Savings,0))

To array-enter a formula, hold down <ctrl><shift> while hitting <enter>. Excel
will place braces {...} around the formula.


--ron