+ Reply to Thread
Results 1 to 12 of 12

A series of weighted variables for a percentage ranking

  1. #1
    Registered User
    Join Date
    03-03-2010
    Location
    New York, NY
    MS-Off Ver
    Excel 2003
    Posts
    7

    A series of weighted variables for a percentage ranking

    Hello,

    I've attached the excel file that showcases the break out.

    Ultimately there 6 vendors (labeled A - F) the various categors and each vendors ranking within each out 6 categories left to right. How do I calculate a combined weighted ranking formula?:
    - Category 1, weighted 20%, - (value 1 - 100)
    - Category 2, weighted 20%, - (value 1 - 5, w/ 5 being the highest)
    - Category 3, weighted 20%, - (value 1 - 5, w/ 5 being the highest)
    - Category 4, weighted 20%, - (value 1 - 100)
    - Category 5, weighted 10%, - (value 1 - 5, w/ 1 being the highest)
    - Category 6, weighted 10%, - (value 1 - 5, w/ 5 being the highest)

    Categories (weighted)>>>>>>
    A 75 4 2 55 5 4 = (A % Ranking result Formula???? )
    B 65 2 5 70 4 3 = (B % Ranking result Formula???? )
    C 70 4 4 66 3 5 = (C % Ranking result Formula???? )
    D 45 1 3 40 3 3 = (D % Ranking result Formula???? )
    E 55 3 3 52 5 3 = (E % Ranking result Formula???? )
    F 45 1 4 80 2 4 = (F % Ranking result Formula???? )
    Attached Files Attached Files
    Last edited by DonkeyOte; 03-03-2010 at 03:21 AM. Reason: "Formula help!!!:" removed from title

  2. #2
    Valued Forum Contributor rwgrietveld's Avatar
    Join Date
    09-02-2008
    Location
    Netherlands
    MS-Off Ver
    XL 2007 / XL 2010
    Posts
    1,671

    Re: Formula help!!!: A series of weighted variables for a percentage ranking

    Not quit sure what the end result should be (in %, scale), but lets start here based on you formula:

    HTML Code: 
    =(RANK(B7,$B$7:$B$12)*0.2+RANK(C7,$C$7:$C$12)*0.2+RANK(D7,$D$7:$D$12)*0.2+RANK(E7,$E$7:$E$12)*0.2+RANK(F7,$F$7:$F$12)*0.1+RANK(G7,$G$7:$G$12)*0.1)/6
    This will give you a percentage 0-100% where values closest to 0% are the best results (highest avg rank)
    Last edited by rwgrietveld; 03-03-2010 at 03:21 AM.
    Looking for great solutions but hate waiting?
    Seach this Forum through Google

    www.Google.com
    (e.g. +multiple +IF site:excelforum.com/excel-general/ )

    www.Google.com
    (e.g. +fill +combobox site:excelforum.com/excel-programming/ )

    Ave,
    Ricardo

  3. #3
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: A series of weighted variables for a percentage ranking

    buzzzworthy - please endeavour to post in the most relevant forum - I've requested this thread be moved to a question Forum - specifically Excel Worksheet Functions.

  4. #4
    Registered User
    Join Date
    03-03-2010
    Location
    New York, NY
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Formula help!!!: A series of weighted variables for a percentage ranking

    Quote Originally Posted by rwgrietveld View Post
    Not quit sure what the end result should be (in %, scale), but lets start here based on you formula:

    HTML Code: 
    =(RANK(B7,$B$7:$B$12)*0.2+RANK(C7,$C$7:$C$12)*0.2+RANK(D7,$D$7:$D$12)*0.2+RANK(E7,$E$7:$E$12)*0.2+RANK(F7,$F$7:$F$12)*0.1+RANK(G7,$G$7:$G$12)*0.1)/6
    This will give you a percentage 0-100% where values closest to 0% are the best results (highest avg rank)

    Thanks so much for you speedy response,

    The end result: based on the combined weighted results for each vendor, we need an overall best ranking score by vendor as a percentage.

    Special note in Column F the ranking 1- 5 is reversed where 1 is the highestest ranking and 5 is the lowest, did we already account for this unique scoring variable?

    Also if we run a test calculation on the best possible scoring totals across all categories a vendor can make the ranking should reflect it. with the highest qualifying percentage , no? What do you think, I'm not sure if we accounted for this?

    Thanks so much,

    buzzzzz

  5. #5
    Registered User
    Join Date
    03-03-2010
    Location
    New York, NY
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: A series of weighted variables for a percentage ranking

    Quote Originally Posted by DonkeyOte View Post
    buzzzworthy - please endeavour to post in the most relevant forum - I've requested this thread be moved to a question Forum - specifically Excel Worksheet Functions.
    Thanks for the corrected reference

  6. #6
    Valued Forum Contributor rwgrietveld's Avatar
    Join Date
    09-02-2008
    Location
    Netherlands
    MS-Off Ver
    XL 2007 / XL 2010
    Posts
    1,671

    Re: A series of weighted variables for a percentage ranking

    Based on you additions:

    we need an overall best ranking score by vendor as a percentage.

    Special note in Column F the ranking 1- 5 is reversed where 1 is the highestest ranking and 5 is the lowest

    Also if we run a test calculation on the best possible scoring totals across all categories a vendor can make the ranking should reflect it. with the highest qualifying percentage.
    HTML Code: 
    =(RANK(B7,$B$7:$B$12,1)*0.2+RANK(C7,$C$7:$C$12,1)*0.2+RANK(D7,$D$7:$D$12,0)*0.2+RANK(E7,$E$7:$E$12,1)*0.2+RANK(F7,$F$7:$F$12,1)*0.1+RANK(G7,$G$7:$G$12,1)*0.1)/6
    This will give you a percentage 0-100% where values closest to 100% are the best results (highest avg rank)

  7. #7
    Registered User
    Join Date
    03-03-2010
    Location
    New York, NY
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: A series of weighted variables for a percentage ranking

    Quote Originally Posted by rwgrietveld View Post
    Based on you additions:



    HTML Code: 
    =(RANK(B7,$B$7:$B$12,1)*0.2+RANK(C7,$C$7:$C$12,1)*0.2+RANK(D7,$D$7:$D$12,0)*0.2+RANK(E7,$E$7:$E$12,1)*0.2+RANK(F7,$F$7:$F$12,1)*0.1+RANK(G7,$G$7:$G$12,1)*0.1)/6
    This will give you a percentage 0-100% where values closest to 100% are the best results (highest avg rank)
    Thanks the score did improve, but it's not adding up to 100% when I entered the highest possible scoring numbers in each category.

    I've attached also the version 2 of the file with this revised formula on the first tab. I've also called out the key factors that are affecting the overall calculation.

    Let me know if this helps,

    Thanks again in advance
    Attached Files Attached Files

  8. #8
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: A series of weighted variables for a percentage ranking

    Would:

    H7: =$B7/100*0.2+$C7/5*0.2+$D7/5*0.2+$E7/100*0.2+(6-$F7)/5*0.1+$G7/5*0.1
    copied down

    work for you ?

  9. #9
    Valued Forum Contributor rwgrietveld's Avatar
    Join Date
    09-02-2008
    Location
    Netherlands
    MS-Off Ver
    XL 2007 / XL 2010
    Posts
    1,671

    Re: A series of weighted variables for a percentage ranking

    Put this in H7: =0.2*SUM(B7/100+C7/5+D7/5+E7/100+0.5*(6-F7)/5+0.5*G7/5)

    I was confused when you used the word ranking

  10. #10
    Registered User
    Join Date
    03-03-2010
    Location
    New York, NY
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: A series of weighted variables for a percentage ranking

    Eureka!!

    Both of these formulas worked perfectly.

    Thanks guys, you rock!

    -Buzzzz

  11. #11
    Registered User
    Join Date
    03-03-2010
    Location
    New York, NY
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: A series of weighted variables for a percentage ranking

    Quote Originally Posted by rwgrietveld View Post
    Put this in H7: =0.2*SUM(B7/100+C7/5+D7/5+E7/100+0.5*(6-F7)/5+0.5*G7/5)

    I was confused when you used the word ranking

    I just wanted to rate your response.

    Thanks again,

    -buzzz

  12. #12
    Registered User
    Join Date
    03-03-2010
    Location
    New York, NY
    MS-Off Ver
    Excel 2003
    Posts
    7

    Thumbs up Re: A series of weighted variables for a percentage ranking

    Quote Originally Posted by DonkeyOte View Post
    Would:

    H7: =$B7/100*0.2+$C7/5*0.2+$D7/5*0.2+$E7/100*0.2+(6-$F7)/5*0.1+$G7/5*0.1
    copied down

    work for you ?
    I wanted to rate your response as well.

    Thanks again,

    -buzzz

+ 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