How to find best option from a range based on weighted values

[Drew_]

Hi all.

I think I have a unique question here. I have a range of various values (10 columns) that are based on different weapon stats for a game I play. Sample data below:

[TABLE="width: 793"]
<colgroup><col span="4"><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Weapon[/TD]
[TD]Class[/TD]
[TD]Mag Size[/TD]
[TD]RPM[/TD]
[TD]Reload (1 bullet)[/TD]
[TD]Damage[/TD]
[TD]Drop off[/TD]
[TD]Left Recoil[/TD]
[TD]Vertical Recoil[/TD]
[TD]Right Recoil[/TD]
[TD]1st shot recoil[/TD]
[TD]ADS [/TD]
[/TR]
[TR]
[TD]FAMAS[/TD]
[TD]Assault[/TD]
[TD]2.6[/TD]
[TD]50[/TD]
[TD]0.458[/TD]
[TD]5[/TD]
[TD]2.5[/TD]
[TD]0.035[/TD]
[TD]0.035[/TD]
[TD]0.055[/TD]
[TD]0.13[/TD]
[TD]0.02[/TD]
[/TR]
[TR]
[TD]AEK 971[/TD]
[TD]Assault[/TD]
[TD]3.1[/TD]
[TD]45[/TD]
[TD]0.383[/TD]
[TD]5[/TD]
[TD]2.5[/TD]
[TD]0.050[/TD]
[TD]0.020[/TD]
[TD]0.030[/TD]
[TD]0.15[/TD]
[TD]0.015[/TD]
[/TR]
[TR]
[TD]F2000[/TD]
[TD]Assault[/TD]
[TD]3.1[/TD]
[TD]42.5[/TD]
[TD]0.375[/TD]
[TD]5[/TD]
[TD]2.5[/TD]
[TD]0.050[/TD]
[TD]0.026[/TD]
[TD]0.040[/TD]
[TD]0.15[/TD]
[TD]0.02[/TD]
[/TR]
[TR]
[TD]KH2002[/TD]
[TD]Assault[/TD]
[TD]3.1[/TD]
[TD]40[/TD]
[TD]0.435[/TD]
[TD]5[/TD]
[TD]2.5[/TD]
[TD]0.040[/TD]
[TD]0.020[/TD]
[TD]0.040[/TD]
[TD]0.075[/TD]
[TD]0.01[/TD]
[/TR]
[TR]
[TD]M16A3[/TD]
[TD]Assault[/TD]
[TD]3.1[/TD]
[TD]40[/TD]
[TD]0.270[/TD]
[TD]5[/TD]
[TD]2.5[/TD]
[TD]0.010[/TD]
[TD]0.026[/TD]
[TD]0.040[/TD]
[TD]0.125[/TD]
[TD]0.01[/TD]
[/TR]
[TR]
[TD]M16A4[/TD]
[TD]Assault[/TD]
[TD]3.1[/TD]
[TD]40[/TD]
[TD]0.270[/TD]
[TD]5[/TD]
[TD]2.5[/TD]
[TD]0.008[/TD]
[TD]0.026[/TD]
[TD]0.030[/TD]
[TD]0.125[/TD]
[TD]0.005[/TD]
[/TR]
</tbody>[/TABLE]



Essentially, I want Excel to tell me which weapon is the best by taking all the stats into consideration. I figured I could do this by assigning weighted values to each column but I'm not sure. Problem is, bigger numbers are not always better (for example, longer reload time is bad, want lower number).

I know this is unique and might not make much sense, but any help would be appreciated. Thanks

 

[NiMip]

Okay, I figured out your suggestions and they make sense to me now. Except, my negative attributes (in red) are still way too influential and I think its because I am taking the inverse. Why exactly am I doing this? The max "score" I can have for my good attributes (in green) is .05 while the max score for my red attributes is 25.62. Do you possibly know of a way around this?

Unfortunately, changing the weights has very minimal effect on the overall score so that is not a sufficient fix.

I think the normalisation thing should fix that? Though the inverse thing does worry me a bit... maybe they need to be normalised after taking the inverse, otherwise an ADS score of 0,25 would contribute 4 before the weightings... which isn't on the normalised scale.

Gah, I dunno. I'm not a mathematician or anything haha, mostly figuring this out as I go :P I'll have a bit of a play and see what I come up with.

 

[Drew_]

Yes, after more review I understood your normalization idea. Great thinking, I applied it to my table.

Yes, there are a lot more weapons, roughly 45. However, taking the inverse of the negative attributes is causing me to place too much emphasis on them. The max score for the red numbers is around 25 while the max score the green numbers is .05; I think we need to find a way to balance that out. Unfortunately, changing the weight percentages does not do a whole lot of anything

EDIT: Just saw your second reply, I will try taking the inverse and then normalizing, give me a minute

 

[Drew_]

Taking the inverse and then normalizing does not work because we can't normalize the scores, we have to normalize the data. The whole "big is bad" concept is really throwing us off

 

[NiMip]

It is doable! I had a muck around with it and came up with the following. The "bad" ones have been inversely normalised around the lowest score, so the lowest score contributes 1, and anything else <1. You should then be able to tweak your weightings as you see fit and the sumproduct is taken across the whole lot, as they're now all 0 = worst, 1 = best.

I've also multiplied the sumproduct by 10 just so you get a score out of ten, rather than 0-1 result... not important, I just like it more.

[TABLE="class: grid, width: 763"]
<tbody>[TR]
[TD]Weapon[/TD]
[TD]Class[/TD]
[TD]Mag Size[/TD]
[TD]Damage[/TD]
[TD]Drop off[/TD]
[TD]Reload (1 bullet)[/TD]
[TD]Left Recoil[/TD]
[TD]Vertical Recoil[/TD]
[TD]Right Recoil[/TD]
[TD]1st shot recoil[/TD]
[TD]ADS[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Weights[/TD]
[TD]---- >[/TD]
[TD="align: right"]7.5%[/TD]
[TD="align: right"]10.0%[/TD]
[TD="align: right"]5.0%[/TD]
[TD="align: right"]12.5%[/TD]
[TD="align: right"]15.0%[/TD]
[TD="align: right"]15.0%[/TD]
[TD="align: right"]15.0%[/TD]
[TD="align: right"]12.5%[/TD]
[TD="align: right"]7.5%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FAMAS[/TD]
[TD]Assault[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]3.05[/TD]
[TD="align: right"]0.35[/TD]
[TD="align: right"]0.35[/TD]
[TD="align: right"]0.55[/TD]
[TD="align: right"]2.6[/TD]
[TD="align: right"]0.4[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]AEK 971[/TD]
[TD]Assault[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]2.55[/TD]
[TD="align: right"]0.5[/TD]
[TD="align: right"]0.2[/TD]
[TD="align: right"]0.3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0.3[/TD]
[TD="align: right"]9.22[/TD]
[/TR]
[TR]
[TD]F2000[/TD]
[TD]Assault[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]2.5[/TD]
[TD="align: right"]0.5[/TD]
[TD="align: right"]0.26[/TD]
[TD="align: right"]0.4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0.4[/TD]
[TD="align: right"]8.86[/TD]
[/TR]
[TR]
[TD]KH2002[/TD]
[TD]Assault[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]2.9[/TD]
[TD="align: right"]0.4[/TD]
[TD="align: right"]0.2[/TD]
[TD="align: right"]0.4[/TD]
[TD="align: right"]1.5[/TD]
[TD="align: right"]0.2[/TD]
[TD="align: right"]9.33[/TD]
[/TR]
[TR]
[TD]M16A3[/TD]
[TD]Assault[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]1.8[/TD]
[TD="align: right"]0.1[/TD]
[TD="align: right"]0.26[/TD]
[TD="align: right"]0.4[/TD]
[TD="align: right"]2.5[/TD]
[TD="align: right"]0.2[/TD]
[TD="align: right"]10.27[/TD]
[/TR]
[TR]
[TD]M16A4[/TD]
[TD]Assault[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]1.8[/TD]
[TD="align: right"]0.075[/TD]
[TD="align: right"]0.26[/TD]
[TD="align: right"]0.3[/TD]
[TD="align: right"]2.5[/TD]
[TD="align: right"]0.1[/TD]
[TD="align: right"]11.27[/TD]
[/TR]
[TR]
[TD]M416[/TD]
[TD]Assault[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]1.75[/TD]
[TD="align: right"]0.1[/TD]
[TD="align: right"]0.26[/TD]
[TD="align: right"]0.3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0.2[/TD]
[TD="align: right"]10.41[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Normalised[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Weapon[/TD]
[TD]Class[/TD]
[TD]Mag Size[/TD]
[TD]Damage[/TD]
[TD]Drop off[/TD]
[TD]Reload (1 bullet)[/TD]
[TD]Left Recoil[/TD]
[TD]Vertical Recoil[/TD]
[TD]Right Recoil[/TD]
[TD]1st shot recoil[/TD]
[TD]ADS[/TD]
[TD]Score[/TD]
[/TR]
[TR]
[TD]Weights[/TD]
[TD]---- >[/TD]
[TD="align: right"]7.5%[/TD]
[TD="align: right"]10.0%[/TD]
[TD="align: right"]5.0%[/TD]
[TD="align: right"]12.5%[/TD]
[TD="align: right"]15.0%[/TD]
[TD="align: right"]15.0%[/TD]
[TD="align: right"]15.0%[/TD]
[TD="align: right"]12.5%[/TD]
[TD="align: right"]7.5%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FAMAS[/TD]
[TD]Assault[/TD]
[TD="align: right"]=C3/MAX(C$3:C$9)[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]=MIN(F$3:F$9)/F3[/TD]
[TD="align: right"]0.57377[/TD]
[TD="align: right"]0.214286[/TD]
[TD="align: right"]0.571429[/TD]
[TD="align: right"]0.545455[/TD]
[TD="align: right"]0.576923[/TD]
[TD="align: right"]0.25[/TD]
[TD="align: right"]=SUMPRODUCT(C14:K14,$C$13:$K$13)*10[/TD]
[/TR]
[TR]
[TD]AEK 971[/TD]
[TD]Assault[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0.686275[/TD]
[TD="align: right"]0.15[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0.5[/TD]
[TD="align: right"]0.333333[/TD]
[TD="align: right"]7.21[/TD]
[/TR]
[TR]
[TD]F2000[/TD]
[TD]Assault[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0.7[/TD]
[TD="align: right"]0.15[/TD]
[TD="align: right"]0.769231[/TD]
[TD="align: right"]0.75[/TD]
[TD="align: right"]0.5[/TD]
[TD="align: right"]0.25[/TD]
[TD="align: right"]6.44[/TD]
[/TR]
[TR]
[TD]KH2002[/TD]
[TD]Assault[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0.603448[/TD]
[TD="align: right"]0.1875[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0.75[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0.5[/TD]
[TD="align: right"]7.54[/TD]
[/TR]
[TR]
[TD]M16A3[/TD]
[TD]Assault[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0.972222[/TD]
[TD="align: right"]0.75[/TD]
[TD="align: right"]0.769231[/TD]
[TD="align: right"]0.75[/TD]
[TD="align: right"]0.6[/TD]
[TD="align: right"]0.5[/TD]
[TD="align: right"]7.99[/TD]
[/TR]
[TR]
[TD]M16A4[/TD]
[TD]Assault[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0.972222[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0.769231[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0.6[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9.12[/TD]
[/TR]
[TR]
[TD]M416[/TD]
[TD]Assault[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0.75[/TD]
[TD="align: right"]0.769231[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0.75[/TD]
[TD="align: right"]0.5[/TD]
[TD="align: right"]8.59

[/TD]
[/TR]
</tbody>[/TABLE]

 

[Drew_]

Okay yes! This makes sense, I see where our issue was. However, I am getting MUCH smaller numbers than you (I added a few categories, maybe that's why?). If it would be easier for me to email my file to you I can certainly do that. Thanks for all your help!

 

[NiMip]

Oh ok, not sure why you'd be getting smaller numbers yeah.
Yeah go for it. I would prefer a dropbox or google drive link to email if that's ok.

 

[Drew_]

Okay what is the best way to do that? I don't have your contact or email (I do not have Dropbox)

 

[Drew_]

https://drive.google.com/open?id=1RRoE4cL-oT_euEJhuyVr_bHAJOf8OZADKR2uBkEl9kc

I'm dumb let me know if it doesnt work

 

[NiMip]

Looks ok to me!
I just realised that in my last post with the table, I didn't delete the numbers in the top of the right-hand column, next to the raw data. They're from the old method and weren't meant to be there... so the values above 10 and 11 aren't correct, just the ones next to the coloured heading-ed table are.

 

[Drew_]

Unfortunately, the numbers are not correct. The weapons with the highest scores are not the correct ones at all. I will try to find another solution. Thank you for all of your help!!