Make Ratings Equal Weight Across Factors

Joined
Jul 5, 2023
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Hi there,
I'm working on an spreadsheet that compares different cities according to population growth, crime index, etc. It then provides a rating for each city relative to that factor compared to the other cities on the list. The ratings for the different factors are then added together to get an overview of which city performs the best across all factors.

My question: How do I make the ratings for each factor equal in weight?

I've attached my spreadsheet and highlighted the column headers I'm asking about.

Thanks for taking a look and any suggestions!

Comparing Cities.xlsx
ABCDEFGHIJKLMNACADAEAFAGAHAI
1Population GrowthCrime Severity IndexUnemployment RateJob GrowthPrice to Rent RatioProperty Tax Rate
2CityProvinceTotal ChangeRelative to totalRating Equal to Other Factors2021Relative to totalRating Equal to Other Factors2021Relative to totalRating Equal to Other FactorsGrowth Last 12 MonthsG-JG12 ScoreTotal Job CountRelative to totalRating Equal to Other FactorsCurrentRelative to totalRating Equal to Other FactorsTotal Rating Across All Factors
3Red DeerAlberta48.94%22.24815176.390.079908132.47715212.30.03577726.454770.0299620.996330.029667
4MississaugaOntario17.14%63.5393537.090.01680250.520877140.04072135.958290.0407250.8297380.024707
5BramptonOntario101.73%10.7036637.090.01680250.52087713.30.03868536.246210.0410510.98%#VALUE!
6EdmontonAlberta51.84%21.0031897.450.044146761.3685512.70.0369427.916670.0316180.938670.02795
7CalgaryAlberta48.69%22.3632972.270.032739731.01493212.50.03635823.650960.0267860.714980.02129
8SaskatoonSaskatchewan35.23%30.9102105.970.048006491.4882018.70.02530524.54780.0278021.2531250.037314
9Saint JohnNew Brunswick51.10.02314930.71762810.90.03170418.122920.0205251.710.050918
10BurlingtonOntario23.94%45.4806922.830.010342440.32061610.70.03112335.947710.0407130.7795830.023213
11OakvilleOntario47.69%22.8336322.830.010342440.32061611.70.03403132.894740.0372560.7323240.021806
12ReginaSaskatchewan27.03%40.27954116.810.052917221.6404349.90.02879617.346670.0196461.0302280.030676
13TorontoOntario12.61%86.3642557.80.026184530.81172113.90.0404332.658390.0369880.6319330.018817
14MarkhamOntario62.26%17.488457.80.026184530.81172113.80.0401441.666670.047190.6450170.019206
15VaughanOntario77.51%14.0484757.80.026184530.81172112.50.03635838.901860.0440590.6827840.020331
16EtobicokeOntario57.80.026184530.811721016.860690.0190960
17ScarboroughOntario57.80.026184530.811721000
18HalifaxNova Scotia22.47%48.449165.720.029772450.92294611.40.03315922.664060.0256691.0840.032278
19OshawaOntario26.13%41.67335410.018510380.57382215.20.04421235.601130.0403211.3256250.039472
20WhitbyOntario58.44%18.63077410.018510380.57382212.30.03577738.274180.0433481.1470780.034156
21AjaxOntario71.74%15.1771740.860.018510380.57382213.40.03897634.998680.0396381.12%#VALUE!
22St John'sNewfoundland & Labrador11.44%95.2091267.860.030741910.952999023.852120.0270142.27170.067643
23HamiltonOntario16.13%67.5014466.310.030039730.93123212.30.03577735.333330.0400171.2452490.037079
24LethbridgeAlberta46.06%23.64047128.650.058280971.806719.40.02734120.975380.0237561.146820.034148
25St. Catherines-NiagaraOntario5.91%184.263862.120.028141580.87238915.80.04595719.331570.0218941.4321670.042645
26WinnipegManitoba20.99%51.8671113.550.051440381.5946528.90.02588722.906090.0259432.80150.083419
27BrandonManitoba29.20%37.29009112.650.051032661.5820125.80.0168720.042190.0226991.5480.046094
28LondonOntario25.49%42.716780.990.036690061.13739213.30.03868517.547030.0198731.4223080.042351
29FrederictonNew Brunswick32.71%33.2902975.50.034202981.0602928.60.02501517.297850.0195911.43110.042613
30CambridgeOntario25.47%42.7580674.950.033953821.05256811.50.0334518.88840.0213921.232580.036702
31KitchenerOntario34.92%31.1822974.950.033953821.052568110.03199519.203680.0217491.1411220.033978
32WaterlooOntario40.32%27.0064374.950.033953821.052568120.03490421.723660.0246041.1317850.0337
33PickeringOntario13.83%78.76028012.90.03752237.187290.0421171.1011910.032789
34Richmond HillOntario53.01%20.53988013.10.03810443.103450.0488180.670650.01997
351088.86%43.352392,207.410.03225806343.8882.949833.58367
Sheet2


Logan
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Hi Logan, welcome to the forum!

I'm not sure if this is exactly what you mean, but when I first looked at this, I thought you could express each figure in the 'Relative to total' columns as a percentage of the total of that column - then multiply by whatever factor you want. This would lead to the following (for example) with the data you provided above:
Book1
ABCDEFGHIJK
1Population GrowthCrime Severity IndexUnemployment Rate
2CityProvinceTotal ChangeRelative to totalRating Equal to Other Factors2021Relative to totalRating Equal to Other Factors2021Relative to totalRating Equal to Other Factors
3Red DeerAlberta0.489422.248151.77176.390.0799088.2112.30.0357773.58
4MississaugaOntario0.171463.539355.0537.090.0168021.73140.0407214.07
5BramptonOntario1.017310.703660.8537.090.0168021.7313.30.0386853.87
6EdmontonAlberta0.518421.003181.6797.450.0441474.5312.70.036943.69
7CalgaryAlberta0.486922.363291.7872.270.032743.3612.50.0363583.64
8SaskatoonSaskatchewan0.352330.91022.46105.970.0480064.938.70.0253052.53
9Saint JohnNew Brunswick0.0051.10.0231492.3810.90.0317043.17
10BurlingtonOntario0.239445.480693.6222.830.0103421.0610.70.0311233.11
11OakvilleOntario0.476922.833631.8222.830.0103421.0611.70.0340313.40
12ReginaSaskatchewan0.270340.279543.20116.810.0529175.439.90.0287962.88
13TorontoOntario0.126186.364256.8757.80.0261852.6913.90.040434.04
14MarkhamOntario0.622617.48841.3957.80.0261852.6913.80.040144.01
15VaughanOntario0.775114.048471.1257.80.0261852.6912.50.0363583.64
16EtobicokeOntario0.0057.80.0261852.6900.00
17ScarboroughOntario0.0057.80.0261852.6900.00
18HalifaxNova Scotia0.224748.44913.8565.720.0297723.0611.40.0331593.32
19OshawaOntario0.261341.673353.31410.018511.9015.20.0442124.42
20WhitbyOntario0.584418.630771.48410.018511.9012.30.0357773.58
21AjaxOntario0.717415.177171.2140.860.018511.9013.40.0389763.90
22St John'sNewfoundland & Labrador0.114495.209127.5767.860.0307423.1600.00
23HamiltonOntario0.161367.501445.3766.310.030043.0812.30.0357773.58
24LethbridgeAlberta0.460623.640471.88128.650.0582815.989.40.0273412.73
25St. Catherines-NiagaraOntario0.0591184.263814.6662.120.0281422.8915.80.0459574.60
26WinnipegManitoba0.209951.86714.13113.550.051445.288.90.0258872.59
27BrandonManitoba0.29237.290092.97112.650.0510335.245.80.016871.69
28LondonOntario0.254942.71673.4080.990.036693.7713.30.0386853.87
29FrederictonNew Brunswick0.327133.290292.6575.50.0342033.518.60.0250152.50
30CambridgeOntario0.254742.758063.4074.950.0339543.4911.50.033453.34
31KitchenerOntario0.349231.182292.4874.950.0339543.49110.0319953.20
32WaterlooOntario0.403227.006432.1574.950.0339543.49120.0349043.49
33PickeringOntario0.138378.760286.2600.0012.90.0375223.75
34Richmond HillOntario0.530120.539881.6300.0013.10.0381043.81
3510.888643.352392207.410.032258343.8
Sheet1
Cell Formulas
RangeFormula
E3:E34,K3:K34,H3:H34E3=D3/(SUM(D$3:D$34))*100
 
Upvote 0
Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,246
Members
452,623
Latest member
cliftonhandyman

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