MATCH INDEX MIN and 2nd MIN

[Mark McDonough]

I have 8 suppliers with their prices listed in columns beneath their names.
With the help of many in here, I can achieve finding out who is the minimum
supplier and who is the 2nd minimum supplier.

Suppose all the suppliers are listed in columns D to K and some suppliers
haven't quoted for all parts listed and in certain sections I have 2
suppliers that did not submit prices for any parts and have zero values
right down their list of prices.

To obtain the minimum supplier, I have used the following formula

=INDEX($D$1:$K$1,1,MATCH(V2,$D$2:$K$2,0)) where V2 contains the minimum
value. The minimum value is obtained by =MIN(IF($D$2:$K$2)=0,"",$D$2:$K$2)
and entered as an array formula.

This successfully tells me who the minimum supplier is for each listed part.

To obtain the 2nd minimum supplier, I use the following formula

=INDEX($D$1:$K$1,1,MATCH(SMALL($D$2:$K$2,2),$D$2:$K$2,0))

and this successfully gives me the 2nd minimum supplier.

However, I find this doesn't work where some of the suppliers have zero
prices as it shows that the minimum supplier is actually a supplier who did
not submit a price. I have tried all sorts of solutions but with no success.
The obvious thing to do is to blank out zero amounts but that is not
practical and somewhat clumsy.

Can anyone provide me with a correction here to ensure I have a water tight
formula that will work regardless of the submitted prices. All help greatly
appreciated.

Mark.

 

[Bob Phillips]

Second smallest

=INDEX($D$1:$K$1,1,MATCH(SMALL(IF($D$2:$K$2>0,$D$2:$K$2),2),$D$2:$K$2,0))

as an array formula

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

I have 8 suppliers with their prices listed in columns beneath their names.
With the help of many in here, I can achieve finding out who is the minimum
supplier and who is the 2nd minimum supplier.

Suppose all the suppliers are listed in columns D to K and some suppliers
haven't quoted for all parts listed and in certain sections I have 2
suppliers that did not submit prices for any parts and have zero values
right down their list of prices.

To obtain the minimum supplier, I have used the following formula

=INDEX($D$1:$K$1,1,MATCH(V2,$D$2:$K$2,0)) where V2 contains the minimum
value. The minimum value is obtained by =MIN(IF($D$2:$K$2)=0,"",$D$2:$K$2)
and entered as an array formula.

This successfully tells me who the minimum supplier is for each listed part.

To obtain the 2nd minimum supplier, I use the following formula

=INDEX($D$1:$K$1,1,MATCH(SMALL($D$2:$K$2,2),$D$2:$K$2,0))

and this successfully gives me the 2nd minimum supplier.

However, I find this doesn't work where some of the suppliers have zero
prices as it shows that the minimum supplier is actually a supplier who did
not submit a price. I have tried all sorts of solutions but with no success.
The obvious thing to do is to blank out zero amounts but that is not
practical and somewhat clumsy.

Can anyone provide me with a correction here to ensure I have a water tight
formula that will work regardless of the submitted prices. All help greatly
appreciated.

Mark.






----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption

=----

 

[Mark McDonough]

Thanks Bob. I'll give it a go at work tomorrow.

Second smallest

=INDEX($D$1:$K$1,1,MATCH(SMALL(IF($D$2:$K$2>0,$D$2:$K$2),2),$D$2:$K$2,0))

as an array formula

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)


=----