MATCH INDEX MIN and 2nd MIN

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 =----
0
markmcd (33)
8/22/2006 12:32:55 PM
excel 39879 articles. 2 followers. Follow

2 Replies
487 Views

Similar Articles

[PageSpeed] 13

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)

"Mark McDonough" <markmcd@bigpond.net.au> wrote in message
news:1156249319_7859@sp6iad.superfeed.net...
> 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
=----


0
bob.NGs1 (1661)
8/22/2006 1:10:38 PM
Thanks Bob. I'll give it a go at work tomorrow.


"Bob Phillips" <bob.NGs@somewhere.com> wrote in message 
news:edgkfxexGHA.1272@TK2MSFTNGP05.phx.gbl...
> 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)
>
> "Mark McDonough" <markmcd@bigpond.net.au> wrote in message
> news:1156249319_7859@sp6iad.superfeed.net...
>> 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
> =----
>
> 



----== 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 =----
0
markmcd (33)
8/22/2006 1:23:53 PM
Reply:

Similar Artilces:

Drawing Page Orientation Doesn't Match Printer Paper Orientation #2
Version: 2008 Operating System: Mac OS X 10.4 (Tiger) Processor: Power PC The thumbnail preview shows that the drawing page orientation doesn't match the printer paper orientation. I can not get the page orientation to match. When I change it to letter, I can see the document change to landscape. When I change the page orientation to landscape, it changes the document to portrait. The two will not be the same. This is in Word 2008, it also happened in an older version of Word. Help, what do I do? ...

How to change the index/indexer server back to its old name??
Our WSS search server (Search Server Express I think) thinks its server is called X instead of Y. Any attempt to work with the crawling rules results in an error, it thinks the search service is offline, though search service really is running. Is there any way I can get the search server to know that it should look for a server called Y instead of X?? Thank you... ------=_NextPart_0001_8F0E7021 Content-Type: text/plain Content-Transfer-Encoding: 7bit First I would add a host file entry or a DNS entry for the old name to get things back up and working as a temporary measu...

Index Match Functions
Has anyone ever combined the Index and Match functions to do lookups? ...

retirement Matching
how do i enter my 401k matching transaction? Thanks Dave See http://www.bollar.org/msmoney/#Q14. <anonymous@discussions.microsoft.com> wrote in message news:079901c3b3a5$23010130$a101280a@phx.gbl... > how do i enter my 401k matching transaction? > Thanks Dave ...

how to match the color of a button with its background?
Hi, I have a owner draw push button. I associate it with two bitmaps, one is down and the other is up. Is there a way to make the bitmaps's background color match the color of the window where the button is located. I manually choose the best matched color but still the result is not good. I can even see a white border line (not drawn by me) when the button is pushed. Please help. Thanks. Tony Check out http://msdn.microsoft.com/library/en-us/vcresed/html/_asug_choosing_an_opaque_or_transparent_background.asp May Young <tony@srac.com> wrote in message news:<408473...

match data to reference then vlookup
I hope that I can explain this.............. I have three seperate spreadsheets each containing data that may match other data in column1 of one another. I do not have the option of sorting the data in any of the three spreadsheets. I need the data from Column55 of each of the spreadsheets wherever the data in column1 is equal or matches. I need to know what spreadsheet1, spreadsheet2 and spreadsheet3 contain in Column5 for all matching data in column1. I need the results of this query to find data into one spreadsheet. Is there an answer other than database? ...

Outlook 2007 Search always returns no matches
Any search I do in Outlook 2007's new search box returns no matches. I watched the Indexing Status as it progressed, and now it says "Outlook has finished indexing all of your items" But, searching on anything, even the word "The" or "a" returns no results. I'm searching all Outlook items. I'm on an Exchange Server via VPN. Anyone have any suggestions? I uninstalled Office and re-installed and the problem went away... "Tom" <none@none.com> wrote in message news:%23yGP6NPOHHA.2140@TK2MSFTNGP03.phx.gbl... > Any search I do i...

Without Matching Query Limit ?
Hi All, The Without Matching query wizard works fine until you select more than 13 columns to compare. My guess is that the 13 " AND " 's in the SQL left join statement are the problem. Unfortunely, my tables have 16 fields to compare. Any sugestions ? Stuart wrote: >The Without Matching query wizard works fine until you select more than 13 >columns to compare. My guess is that the 13 " AND " 's in the SQL left join >statement are the problem. >Unfortunely, my tables have 16 fields to compare. Don't use the wizard ;-) Seriously, after you d...

Matching data in different sheets but slight difference in time va
Hi Please help. I have two sheets as follows: Sheet 1: Date / Time / Number Dailled / Duration /Cost 22/1/10 10:00 01234123456 00:01:57 0.789 Sheet 2: Date / Time / Number Dailled / Duration /Extension 22/1/10 10:02 01234123456 00:01:56 1501 I am trying to put the cost of the phone call against the relevant extension on sheet 2 that has dialled the number dialled. Obviously I can match the number dialled in both cases, and the...

matching numbers with images?
Hello, I have thousants of numbers written on column A:A referring to different objects. All thouse objects have similar filenames with numbers written in column A. i.e number say 12345 refers to a camera and on the HDD I have the image file 12345.jpg. Is there a macro solution such that when I click on any number in column A, the image file *.jpg with the same number will automatically open-up?. TIA --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.720 / Virus Database: 476 - Release Date: 14.07.2004 Hi put the following fo...

Match 2 Columns, Return 3rd, Differing Match Types
Hello, I have 3 columns of data in my main table. For example: Blue / 2 / $5 Blue / 4 / $10 Blue / 6 / $15 Green / 3 / $4 Green / 10 / $8 Red / 1 / $1 Red / 4 / $3 What I am looking to do is use some kind if Index/Match function (I think) to look up the color (column A) and number (column B) and have the price returned (column C). I have found lots of solutions for this out there, however the one part I keep getting stuck on is when I need to look up something like the following... Blue / 5 = #N/A. I want the answer to be $10 but since the number 5 is not an exact mat...

Move to match cells
My spreadsheet looks like this: A B C D E 1 000801025-8 009601025-3 2007 000801025-8 2007 2 000801026-9 009601026-4 2004 000801026-9 2004 3 000801028-1 009601028-6 2006 000801028-1 2006 4 000801036-8 009601036-3 2008 000801036-8 2008 5 000801044-5 009601044-0 2007 000801044-5 2007 6 000801044-5 009601055-0 2004 000801055-5 2004 7 000801055-5 009601064-8 2001 000801064-3 2001 8 000801055-5 009601091-2 2006 000801091-7 2006 9 000801064-3 009601095-6 2002 000801095-1 2002 10 000801091-7 009601097-8 1998 0008...

Register & Favorites Don't Match
I have Money 2004. My checking account register shows the correct balance. But in the "Favorites" list on my home page, my checking account balance is many thousands of dollars lower than the actual balance. How did these get out of sync? Thanks for any help. In microsoft.public.money, Larry E. wrote: >I have Money 2004. My checking account register shows the correct balance. >But in the "Favorites" list on my home page, my checking account balance is >many thousands of dollars lower than the actual balance. How did these get >out of sync? Thanks for ...

M2005 newbie Bank Update Accept to match with my pre-entered trans
Brand new user. Used to Quicken. Here's an example of my problem. On 3/10 I write check number 200 to my church as category Charitable Donation. On 3/17, I update my Banking download and M2005 highlights my transaction with an exclamation point and compares my transaction with Bank entry. Bank entry is dated a week after I wrote check, and does not include my church payee info. If I ACCEPT that the transactions are the same, then I end up with a 3/17 transaction line with no payee, and no category. If I say Do Not Merge, then I end up with double entries, one complete that I m...

Matching the Searched Value to the Adjacent Column
Hi, I'm trying to search for the maximum numeric value in one column but I want the data in the adjacent left column which is a Time variable to be displayed as the result. How do I achieve this? I can only think of MATCH, VLOOKUP. The data is quite large. Thank you very much for your help in advance. Richard try this idea =INDEX(C:C,MATCH(MAX(D:D),D:D)) -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com <vamber001@gmail.com> wrote in message news:babc6c6f-ae70-447c-8057-2cfa2cf1a4b6@w24g2000prd.googlegroups.com... > Hi, > > I'm trying ...

90 min -> 1:30
hi, i have a small problem how can i transvere 90 min into 1:30 tks mario Hi Mario Divide 90 by 1440 and format as time. -- HTH. Best wishes Harald Excel MVP Followup to newsgroup only please "Mario" <mmc_vw1200@hotmail.com> skrev i melding news:3f607936$0$26172$91cee783@newsreader02.highway.telekom.at... > hi, > i have a small problem > how can i transvere 90 min into 1:30 > > tks mario > > TKS it workes very well a VERY big THANK YOU "Harald Staff" <harald.staff@nrk.no> schrieb im Newsbeitrag news:u#S8bqGeDHA.1736@TK2MSFT...

Indexing speed
My search/indexing started acting up in the last few weeks (constantly indexing without ever finishing). So I'm trying to rebuild the index. The index count is sloooooly going down. By my count, I have about 10,000 emails throughout folders in my in-box, but Outlook seems to find about 25,000 "items" to index. It has been chugging away for about 3 full days, and is finally down to about 12,000 items. This seems extremely slow to me, does anyone have a good idea of how long it should take to index this many items? I've adjusted the power settings to assure ...

Indexing Service 02-08-05
I just tried to install CRM sales for outlook on a co- workers computer and I saw a note that I haven't seen before when installing it on other computers. It said that we needed indexing service. What is this, why do we need it, and how do I get it? Please advise. Hi Dave, Indexing service is stopped by default. Before you'll be able to install the Sales for Oultook client go into 'Administrative Tools' and then 'Services' on the computer you're trying to install it on. Scrole down the list of services until you see 'Indexing'. Double click on it ...

Match Ranges problem ,
Hi all , and a good day I have 2 ranges , with these values : myRange1 = (ROW) A B C 1 1 2 4 2 1 3 4 3 1 3 5 myRange2 = (ROW) F G H 1 1 3 4 2 2 3 5 3 1 4 5 I need a macro to compare every cell in myRange2 - first row !! , with every cell in myRange1 - first row !! , and , if tha value of every cell in myRange2 is equal with one of the cells of the first row of myRange1 , to change the value with "QQQ" . This must to be done in every row . After checking first row of myRange2 with first row of myRange1 , to go to next row in myRange2 , and compare ...

My print no longer matches my print preview, help?
I have printed on several occassions without incident, suddenly it is compressing text to half page. Any ideas? It is a printer setting. Look for something like *print document on* and a size box. Some sort of zoom feature. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Cheryl" <Cheryl@discussions.microsoft.com> wrote in message news:9D16F72E-EDF1-4DA6-9340-DC01E990913E@microsoft.com... >I have printed on several occassions without incident, suddenly it is > compressing text to half page. Any ideas? ...

How do I get rid of 2nd page? It is blank.
I have a resume in Word and it fits on 1 page. I've got this 2nd page that is totally blank and I don't need it. How do I get rid of it? This is a multi-part message in MIME format. ------=_NextPart_000_0032_01CAEE07.38326910 Content-Type: text/plain; charset="Utf-8" Content-Transfer-Encoding: 7bit See http://sbarnhill.mvps.org/WordFAQs/BlankPage.htm. -- Regards, Jay Freedman Microsoft Word MVP FAQ: http://word.mvps.org Email cannot be acknowledged; please post all follow-ups to the newsgroup so all may benefit. "mssunbeam" <mssu...

International indexes and stocks
I'm trying to use money2006 to find international indexes end stocks from brazil. I need help lars Any particular index/stock you want? Tried using MSN Money to find any? -- Glyn Simpson, Microsoft MVP - Money http://money.mvps.org Check http://money.mvps.org/faq/default.aspx for tips and fixes for MS Money. To send Microsoft your product wishes see http://money.mvps.org/wishes.aspx I do not respond to any unsolicited email regarding Money "lars" <larsk@terra.com.br> wrote in message news:urA%23guOyFHA.1168@TK2MSFTNGP15.phx.gbl... > I'm trying to use mone...

Convert function not working sec to min
I am using the following function and getting the #NAME? error. Can anyone help? =convert(F13, "sec", "mn") F13 currently has the following formula: =SUM(F5:F12) which equals 132.67 which represents the number of seconds recorded. I want to convert that to minutes. Thanks, Dave --- Message posted from http://www.ExcelForum.com/ Hi Dave! Works OK for me! I've replicated your problem statement F13: =SUM(F5:F12) Returns: 132.67 F15: =CONVERT(F13, "sec", "mn") Returns: 2.211167 What problem are you getting? -- Regards Norman Harker MVP (E...

Create an Index for Word Document
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) I have read the help explaination for creating an index, and still cannot figure it out. All I want to have is an index for proper names, last name first, first, and middle. I'm sure it's easy, but I cannot figure it out. Can anyone help? thanks..... In article <59bb3921.-1@webcrossing.JaKIaxP2ac0>, wrm@officeformac.com wrote: > Version: 2008 > Operating System: Mac OS X 10.6 (Snow Leopard) > > I have read the help explaination for creating an index, and still cannot > figure it out....

How to share 2nd calendar created in my mailbox?
Don't know if this is possible - Created a new folder in my mailbox, folder contains calendar iems, called it xyz I now have 2 calendars, 1 my default / origional, the 2nd called xyz If I go to another mailbox, open another users folder, put my username in, type calendar, it only opens my default calendar. There are no options to open additional calendar folders of other users. Is it possible to "share" this 2nd calender simply, or would it have to be an item in pubic folders. Thanks Sukh This is a shot in the dark since I don't have access to an Exchange Server to te...