+ Reply to Thread
Results 1 to 5 of 5

finding lowest cost from supplier list

  1. #1
    Registered User
    Join Date
    06-30-2010
    Location
    swindon, england
    MS-Off Ver
    Office 365
    Posts
    86

    finding lowest cost from supplier list

    Hello everyone, hopefuly someone can help me, with a variation of a problem i was having before.
    I've attached an example spreadsheet which i hope will help explain what i am trying to do, but to basically explain, i'm trying to create a formula to find the lowest cost price of a product from a list of multiple suppliers, while excluding suppliers from a specific location.

    On the sheet titled Raw Data, i have an example list of pricing from each supplier by destiantion. on the sheet titled Final Cost, i want to be able to use a formula to find the lowest cost from the Raw data sheet, but exlcuding the pricing from suppliers in Shanghai.
    Attached Files Attached Files
    Last edited by jimbokeep; 08-05-2010 at 06:04 AM.

  2. #2
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016
    Posts
    3,390

    Re: finding lowest cost from supplier list

    Hi,

    This array in the final cost sheet, cell B2 and copied down

    =MIN(IF('Raw data'!$A$2:$A$14&'Raw data'!$C$2:$C$14='Final Cost'!A2&"Shanghai",'Raw data'!$D$2:$D$14,""))

    Confirm with CTRL, SHIFT and ENTER
    Rule 1: Never merge cells
    Rule 2: See rule 1

    "Tomorrow I'm going to be famous. All I need is a tennis racket and a hat".

  3. #3
    Registered User
    Join Date
    06-30-2010
    Location
    swindon, england
    MS-Off Ver
    Office 365
    Posts
    86

    Re: finding lowest cost from supplier list

    Thanks for the response, and the formulae you have provided is actually better than a similar one i am using elsewhere, however i don't think it solves the issue i was having.
    I'm trying to find the lowest price from the list but excluding prices from Shanghai, and i think this formula will actually find me the price from Shanghai.
    If you could help out here again it would be very much appreciated.

  4. #4
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016
    Posts
    3,390

    Re: finding lowest cost from supplier list

    Sorry - had misread.

    Try this:

    =MIN(IF('Raw data'!$A$2:$A$14='Final Cost'!A2,IF(NOT('Raw data'!$C$2:$C$14="Shanghai"),'Raw data'!$D$2:$D$14,"")))

    also an array, CTRL, SHIFT and ENTER

  5. #5
    Registered User
    Join Date
    06-30-2010
    Location
    swindon, england
    MS-Off Ver
    Office 365
    Posts
    86

    Re: finding lowest cost from supplier list

    thanks sweep, works perfectly.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1