min function

  • Thread starter Thread starter sangeeta
  • Start date Start date
S

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:
 
sangeeta said:
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
Click to expand...


=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
Click to expand...


=INDEX(B$1:E$1,MATCH(MIN(IF(B2:E2<>0,B2:E2)),B2:E2,0))

also an array formula
 
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.
 
Is it possible to get all the countries with minimum values in th
result set?

Thanks.
Sangeet
 
Back
Top