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

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

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

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

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