Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
min function
Hi - Thanks for any help on this. I have a spreadsheet that looks like MATURITY AUSTRIA BELGIUM FINLAND FRANCE 4/24/2006 2.651 0 2.651 2.507 5/18/2006 2.651 2.507 2.651 2.507 7/20/2006 2.779 2.582 0 2.582 10/19/2006 2.932 2.765 2.932 2.765 4/30/2007 3.012 3.198 3.198 3.012 I need help with 2 issues: 1) I need to pick minimum non zero number for a given row 2) I need to know for the minimum as ascertained in 1) , what is the country eg. for 4/24/2006 - minimum is 2.507 and the country is France Thanks. Sangeeta:) -- sangeeta ------------------------------------------------------------------------ sangeeta's Profile: http://www.excelforum.com/member.php...o&userid=33826 View this thread: http://www.excelforum.com/showthread...hreadid=536010 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
min function
"sangeeta" wrote in message ... Hi - Thanks for any help on this. I have a spreadsheet that looks like MATURITY AUSTRIA BELGIUM FINLAND FRANCE 4/24/2006 2.651 0 2.651 2.507 5/18/2006 2.651 2.507 2.651 2.507 7/20/2006 2.779 2.582 0 2.582 10/19/2006 2.932 2.765 2.932 2.765 4/30/2007 3.012 3.198 3.198 3.012 I need help with 2 issues: 1) I need to pick minimum non zero number for a given row =MIN(IF(B2:E2<0,B2:E2)) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. 2) I need to know for the minimum as ascertained in 1) , what is the country eg. for 4/24/2006 - minimum is 2.507 and the country is France =INDEX(B$1:E$1,MATCH(MIN(IF(B2:E2<0,B2:E2)),B2:E2 ,0)) also an array formula |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
min function
Hi Sangeeta
The following is an array formula, so you must commit (or amend) using Ctrl+Shift+Enter not just Enter. Do not type the curly braces { } yourself, Excel will include them for you when you use Ctrl+Shift+Enter {=INDEX($A$1:$E$1,MATCH(MIN(IF(A2:E20,A2:E2,100)) ,A2:E2,0))} This formula does not break ties however. In row 2 of your data where Belgium and France each have values of 2.507 which are both the lowest values, the formula will return the first country in the header row, Belgium. -- Regards Roger Govier "sangeeta" wrote in message ... Hi - Thanks for any help on this. I have a spreadsheet that looks like MATURITY AUSTRIA BELGIUM FINLAND FRANCE 4/24/2006 2.651 0 2.651 2.507 5/18/2006 2.651 2.507 2.651 2.507 7/20/2006 2.779 2.582 0 2.582 10/19/2006 2.932 2.765 2.932 2.765 4/30/2007 3.012 3.198 3.198 3.012 I need help with 2 issues: 1) I need to pick minimum non zero number for a given row 2) I need to know for the minimum as ascertained in 1) , what is the country eg. for 4/24/2006 - minimum is 2.507 and the country is France Thanks. Sangeeta:) -- sangeeta ------------------------------------------------------------------------ sangeeta's Profile: http://www.excelforum.com/member.php...o&userid=33826 View this thread: http://www.excelforum.com/showthread...hreadid=536010 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
min function
That worked - thanks a lot -- sangeeta ------------------------------------------------------------------------ sangeeta's Profile: http://www.excelforum.com/member.php...o&userid=33826 View this thread: http://www.excelforum.com/showthread...hreadid=536010 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
min function
Is it possible to get all the countries with minimum values in the result set? Thanks. Sangeeta -- sangeeta ------------------------------------------------------------------------ sangeeta's Profile: http://www.excelforum.com/member.php...o&userid=33826 View this thread: http://www.excelforum.com/showthread...hreadid=536010 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a Custom Excel Function to Calculate Gini Coefficients | Excel Worksheet Functions | |||
Date & Time | New Users to Excel | |||
Hyperlinks using R[1]C[1] and offset function in its cell referenc | Excel Worksheet Functions | |||
Conversion | Excel Worksheet Functions | |||
HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. | Excel Worksheet Functions |