Thread: A series of weighted variables for a percentage ranking

Originally Posted by rwgrietveld

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

Originally Posted by DonkeyOte

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

Originally Posted by rwgrietveld

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

Eureka!!

Both of these formulas worked perfectly.

Thanks guys, you rock!

-Buzzzz

Originally Posted by rwgrietveld

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

Originally Posted by DonkeyOte

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