|
Return minimum POSITVE value from range
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 -- TheRobsterUK ------------------------------------------------------------------------ TheRobsterUK's Profile: http://www.excelforum.com/member.php...fo&userid=9924 View this thread: http://www.excelforum.com/showthread...hreadid=470975 |
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:A100)*(A3:A10),(A3:A10)))),A 3: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:A100)*(A3:A10),(A3:A 10)))),A3:B10,2,0)),"All values are negative.",VLOOKUP((MIN(IF((A3:A100)*(A3:A10),(A3 :A10)))),A3:B10,2,0)) -- tj "TheRobsterUK" wrote: 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 -- TheRobsterUK ------------------------------------------------------------------------ TheRobsterUK's Profile: http://www.excelforum.com/member.php...fo&userid=9924 View this thread: http://www.excelforum.com/showthread...hreadid=470975 |
Assuming data in cols A & B
Try something like this in say, C1: =MIN(IF(A2:A100,B2:B10)) Array-enter the formula, i.e. press CTRL+SHIFT+ENTER instead of just pressing ENTER Adapt the ranges to suit .. -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "TheRobsterUK" wrote in message news:TheRobsterUK.1vztea_1127783101.6456@excelforu m-nospam.com... 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 -- TheRobsterUK ------------------------------------------------------------------------ TheRobsterUK's Profile: http://www.excelforum.com/member.php...fo&userid=9924 View this thread: http://www.excelforum.com/showthread...hreadid=470975 |
Correction to earlier formula, sorry ..
Try something like this instead in say, C1: =INDEX(B2:B10, MATCH(MIN(IF(A2:A100,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 .... -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
"TheRobsterUK"
wrote in message news:TheRobsterUK.1vztea_1127783101.6456@excelforu m-nospam.com... 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:A100, A3:A10)),A3:B10,2,0),"No positive number")} FormulaArray Bruno |
use function <DMIN
see help ======= "Bruno Campanini" wrote in message ... "TheRobsterUK" wrote in message news:TheRobsterUK.1vztea_1127783101.6456@excelforu m-nospam.com... 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:A100, A3:A10)),A3:B10,2,0),"No positive number")} FormulaArray Bruno |
"R.VENKATARAMAN" wrote in message
... 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 |
On Mon, 26 Sep 2005 19:22:08 -0500, TheRobsterUK
wrote: 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(Savings0,Savings)),Savings ,0)) To array-enter a formula, hold down <ctrl<shift while hitting <enter. Excel will place braces {...} around the formula. --ron |
| All times are GMT +1. The time now is 02:40 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com