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]

Ah that sucks :/ I mean this method is mathematically sound, but might just not be set up the right way. But yeah it's difficult for me to give advice because I don't play the game and hence can't judge what weapons should be rising to the top.

Maybe the outputs should be a ranking for TTK and another ranking for recoil/bad stuff. For me, I want to know that I can kill someone quickly (dmg/sec) and don't have to rely on my skill too much (low recoil). Perhaps you need to roll everything into these headings.

Anyway, best of luck with it! Do let me know if you come up with something that works

 

[Drew_]

Actually, I figured it out. I was not normalizing the TTK before taking the SUMPRODUCT so this was throwing everything off. Once I normalized it I was getting good numbers that made sense

Thank you for all of your help! Much appreciated