+ Reply to Thread
Results 1 to 7 of 7

Ranking Formula

  1. #1
    Registered User
    Join Date
    08-13-2013
    Location
    Brisbane
    MS-Off Ver
    Excel 2010
    Posts
    9

    Ranking Formula

    Hi all,

    Another question on Excel. I'm trying to Rank pricing of items so I can find the cheapest bidder. My table is structured as follows
    1.png

    As you can see, its a file compiled of multiple supplier bids, where the item ID repeats. Suppliers can choose to submit a bid on whatever product they choose.

    Next I do a ranking based on the following formula
    =IF(D2>0,SUMPRODUCT((B:B=B2)*(D:D<D2))+1,"error")
    2.png
    As you can see, this results in an "error" notification if a supplier has not made a bid. Moving on, I create a simple lookup by concatenating the product ID and Rank
    3.png

    Then I create this table, to give me the cheapest bid (vlookup (product id - rank1)). Here is where things start to go wrong
    4.png

    As you can see, I get an 'error' bid for product 1, even though it has a valid bid
    Supplier A has been flagged as the cheapest bid for item 1, even though supplier C has bid an identical price. Looking at the ranking, they have both been given the Rank "1" and Excel picked the first one. Is there a way to modify the ranking formula to give them Rank 1 & 2, even if its alphabetical? It's just that if I do a lookup for Rank 2, I won't get anything.

    Hope that was somewhat clear. I've also attached the spreadsheet for clarification.
    Problem.xlsx

  2. #2
    Registered User
    Join Date
    02-09-2014
    Location
    New York
    MS-Off Ver
    Excel 2013
    Posts
    84

    Re: Ranking Formula

    ranking-formula-problem.xlsx

    NA problem: Formula can't fine 1-1 in your Data Array.
    "1-1" doesn't exist in the Lookup column.

    Did you ever try Pivot table, there are 3 samples in the file link.
    It can be updated with the refresh button when prices are updated.

  3. #3
    Registered User
    Join Date
    08-13-2013
    Location
    Brisbane
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Ranking Formula

    Hi

    Thanks for that. I would try a pivot table, but I have the following issues
    1. I'm expecting around 50 suppliers to bid on 400 items, resulting in a total potential off 20,000 line items, which would make a pivot a little clunky
    2. I'm not going to be doing the evaluation myself. I have to set this up for a basic excel user, to simply cut and paste to generate a dashboard. I also need to avoid macros.

    I realise I may be asking the impossible, but any help in defining a formula that will ignore blanks, and rank equal bids will be much appreciated.

  4. #4
    Valued Forum Contributor
    Join Date
    03-20-2011
    Location
    UK
    MS-Off Ver
    Excel 2007/10/16
    Posts
    840

    Re: Ranking Formula

    Hi

    Maybe this might work for you?
    Find a cheapest bidder!
    Doing 20,000 rows

    F1
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    G1
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    K2
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    copy down where you want it?

    J2
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    This is array formula, Press same time Ctrl+Shift+Enter, NOT ENTER copy down

    I2
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Again array formula, Ctrl+Shift Enter copy down.

    See the file! Hightlight Yellow

    Regard
    Attached Files Attached Files
    To help you by my post? it would be nice to click on to say "Thank you".
    If you are happy with a solution to your problem?
    Click Thread Tools above your first post,
    select "Mark your thread as Solved".

  5. #5
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Ranking Formula

    Pl see attached file with ARRAY formulas.
    Attached Files Attached Files
    Last edited by kvsrinivasamurthy; 08-28-2014 at 06:24 AM.

  6. #6
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Ranking Formula

    find the attached file
    Attached Files Attached Files
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  7. #7
    Valued Forum Contributor
    Join Date
    03-20-2011
    Location
    UK
    MS-Off Ver
    Excel 2007/10/16
    Posts
    840

    Re: Ranking Formula

    nflsales and Kvsrinivasamurthy
    I would have done that!!

    Only reason I did this way, was he got 20,000 rows? That way he can see what cheaper on F1 and how many there was on G1 which is 6.

    There a chance he might not want it that way? I would then next step was do similar nflsales had done!!

    Ruvster. Choice is your?

    Regard
    Last edited by micope21; 08-28-2014 at 06:51 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Ranking values and ranking duplicates the same rank
    By 302arpks in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 03-05-2015, 01:50 PM
  2. [SOLVED] Ranking up to 15 numbers in column D Ranking skips 7 with a tie at 6 and 2
    By Securitysports in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-03-2013, 07:11 AM
  3. [SOLVED] formula for ranking
    By max_max in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-29-2013, 02:05 AM
  4. [SOLVED] Ranking formula Help
    By masond3 in forum Excel General
    Replies: 16
    Last Post: 07-27-2012, 06:51 AM
  5. Replies: 6
    Last Post: 06-01-2010, 06:19 PM

Tags for this Thread

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