min function

[sangeeta]

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 th
country eg. for 4/24/2006 - minimum is 2.507 and the country is France

Thanks.

Sangeeta:

 

[Bob Phillips]

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

 

[Roger Govier]

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:E2>0,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.

 

[sangeeta]

That worked - thanks a lot

 

[sangeeta]

Is it possible to get all the countries with minimum values in th
result set?

Thanks.
Sangeet