+ Reply to Thread
Results 1 to 5 of 5

Ranking scores to return unique values

  1. #1
    Registered User
    Join Date
    11-13-2012
    Location
    Manchester
    MS-Off Ver
    Excel 2010
    Posts
    6

    Question Ranking scores to return unique values

    Hello

    I'm currently trying to work out how to provide a list of ranked options with the description of the option returned.

    On the attached, on the worksheet 'Opportunities Master' I will have a list of opportunities with options to score them against criteria with a simple criteria scoring (1 to 3). Column H then returns the average score.

    On the worksheet 'Opportunities Ranking', I want to return the list according to their rank and will be using Index-match functions to return details about them (have a bit more info in the sheet).

    The problem I have, is that where a value is the same, it returns the first occurence it comes across. So if 3 options all score 2.20, it will just return the first one in the list 3 times.

    I want to find a way to create a unique score (so something like adding the average and then a series of "000000001" plus the row number or something. But when I have tried there are two issues:
    1) if the average is a whole number, it doesn't include a decimal point (so an average score of 2 would return "200000000001" rather than "2.00000000001"
    2) the 'Opportunities Ranking' sheet doesn't seem to recognise them anyway so is blank.

    If there is a better way of doing this, I'm happy as long as it is fit for purpose. I don't really want to use VBA.

    Help please

    Steve

    Ps the list of opportunities would be much longer, hence the need for this.
    Attached Files Attached Files
    Last edited by steve_; 03-22-2013 at 07:39 AM.

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

    Re: Ranking scores to return unique values

    Use this array formula (After entry press Ctrl+Shift+Enter together) in D3 and drag down.

    Please Login or Register  to view this content.

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

    Re: Ranking scores to return unique values

    Hi Steve

    Opportunities Ranking put in column I5 =COUNTIF($H$5:H5,H5)/100+H5, Hide column I

    Opportunities Master
    Column C3 =INDEX('Opportunities Master'!$H$5:$H$11,MATCH(LARGE('Opportunities Master'!$I$5:$I$11,ROW(A1)),'Opportunities Master'!$I$5:$I$11,0),1) copy down,
    Column D3 =INDEX('Opportunities Master'!$B$5:$B$11,MATCH(LARGE('Opportunities Master'!$I$5:$I$11,ROW(A1)),'Opportunities Master'!$I$5:$I$11,0),1) copy down,
    Column E3 =INDEX('Opportunities Master'!$A$5:$A$11,MATCH(LARGE('Opportunities Master'!$I$5:$I$11,ROW(A1)),'Opportunities Master'!$I$5:$I$11,0),1) copy down.

    This is without Array formula.
    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".

  4. #4
    Registered User
    Join Date
    11-13-2012
    Location
    Manchester
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Ranking scores to return unique values

    Super! Thanks a million, I can adapt this to what I need and it suits perfectly

  5. #5
    Registered User
    Join Date
    11-13-2012
    Location
    Manchester
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Ranking scores to return unique values

    Thanks for both solutions

+ 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