Make Ratings Equal Weight Across Factors

[ExcelerateMyLearning]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N															AC	AD	AE	AF	AG	AH	AI
1			Population Growth			Crime Severity Index			Unemployment Rate			Job Growth																	Price to Rent Ratio			Property Tax Rate
2	City	Province	Total Change	Relative to total	Rating Equal to Other Factors	2021	Relative to total	Rating Equal to Other Factors	2021	Relative to total	Rating Equal to Other Factors	Growth Last 12 Months	G-JG12 Score	Total Job Count																Relative to total	Rating Equal to Other Factors	Current	Relative to total	Rating Equal to Other Factors	Total Rating Across All Factors
3	Red Deer	Alberta	48.94%	22.24815		176.39	0.07990813	2.477152	12.3	0.035777																			26.45477	0.029962		0.99633	0.029667
4	Mississauga	Ontario	17.14%	63.53935		37.09	0.0168025	0.520877	14	0.040721																			35.95829	0.040725		0.829738	0.024707
5	Brampton	Ontario	101.73%	10.70366		37.09	0.0168025	0.520877	13.3	0.038685																			36.24621	0.041051		0.98%	#VALUE!
6	Edmonton	Alberta	51.84%	21.00318		97.45	0.04414676	1.36855	12.7	0.03694																			27.91667	0.031618		0.93867	0.02795
7	Calgary	Alberta	48.69%	22.36329		72.27	0.03273973	1.014932	12.5	0.036358																			23.65096	0.026786		0.71498	0.02129
8	Saskatoon	Saskatchewan	35.23%	30.9102		105.97	0.04800649	1.488201	8.7	0.025305																			24.5478	0.027802		1.253125	0.037314
9	Saint John	New Brunswick				51.1	0.0231493	0.717628	10.9	0.031704																			18.12292	0.020525		1.71	0.050918
10	Burlington	Ontario	23.94%	45.48069		22.83	0.01034244	0.320616	10.7	0.031123																			35.94771	0.040713		0.779583	0.023213
11	Oakville	Ontario	47.69%	22.83363		22.83	0.01034244	0.320616	11.7	0.034031																			32.89474	0.037256		0.732324	0.021806
12	Regina	Saskatchewan	27.03%	40.27954		116.81	0.05291722	1.640434	9.9	0.028796																			17.34667	0.019646		1.030228	0.030676
13	Toronto	Ontario	12.61%	86.36425		57.8	0.02618453	0.811721	13.9	0.04043																			32.65839	0.036988		0.631933	0.018817
14	Markham	Ontario	62.26%	17.4884		57.8	0.02618453	0.811721	13.8	0.04014																			41.66667	0.04719		0.645017	0.019206
15	Vaughan	Ontario	77.51%	14.04847		57.8	0.02618453	0.811721	12.5	0.036358																			38.90186	0.044059		0.682784	0.020331
16	Etobicoke	Ontario				57.8	0.02618453	0.811721		0																			16.86069	0.019096			0
17	Scarborough	Ontario				57.8	0.02618453	0.811721		0																				0			0
18	Halifax	Nova Scotia	22.47%	48.4491		65.72	0.02977245	0.922946	11.4	0.033159																			22.66406	0.025669		1.084	0.032278
19	Oshawa	Ontario	26.13%	41.67335		41	0.01851038	0.573822	15.2	0.044212																			35.60113	0.040321		1.325625	0.039472
20	Whitby	Ontario	58.44%	18.63077		41	0.01851038	0.573822	12.3	0.035777																			38.27418	0.043348		1.147078	0.034156
21	Ajax	Ontario	71.74%	15.17717		40.86	0.01851038	0.573822	13.4	0.038976																			34.99868	0.039638		1.12%	#VALUE!
22	St John's	Newfoundland & Labrador	11.44%	95.20912		67.86	0.03074191	0.952999		0																			23.85212	0.027014		2.2717	0.067643
23	Hamilton	Ontario	16.13%	67.50144		66.31	0.03003973	0.931232	12.3	0.035777																			35.33333	0.040017		1.245249	0.037079
24	Lethbridge	Alberta	46.06%	23.64047		128.65	0.05828097	1.80671	9.4	0.027341																			20.97538	0.023756		1.14682	0.034148
25	St. Catherines-Niagara	Ontario	5.91%	184.2638		62.12	0.02814158	0.872389	15.8	0.045957																			19.33157	0.021894		1.432167	0.042645
26	Winnipeg	Manitoba	20.99%	51.8671		113.55	0.05144038	1.594652	8.9	0.025887																			22.90609	0.025943		2.8015	0.083419
27	Brandon	Manitoba	29.20%	37.29009		112.65	0.05103266	1.582012	5.8	0.01687																			20.04219	0.022699		1.548	0.046094
28	London	Ontario	25.49%	42.7167		80.99	0.03669006	1.137392	13.3	0.038685																			17.54703	0.019873		1.422308	0.042351
29	Fredericton	New Brunswick	32.71%	33.29029		75.5	0.03420298	1.060292	8.6	0.025015																			17.29785	0.019591		1.4311	0.042613
30	Cambridge	Ontario	25.47%	42.75806		74.95	0.03395382	1.052568	11.5	0.03345																			18.8884	0.021392		1.23258	0.036702
31	Kitchener	Ontario	34.92%	31.18229		74.95	0.03395382	1.052568	11	0.031995																			19.20368	0.021749		1.141122	0.033978
32	Waterloo	Ontario	40.32%	27.00643		74.95	0.03395382	1.052568	12	0.034904																			21.72366	0.024604		1.131785	0.0337
33	Pickering	Ontario	13.83%	78.76028			0		12.9	0.037522																			37.18729	0.042117		1.101191	0.032789
34	Richmond Hill	Ontario	53.01%	20.53988			0		13.1	0.038104																			43.10345	0.048818		0.67065	0.01997
35			1088.86%	43.35239		2,207.41	0.03225806		343.8																				882.9498			33.58367
Sheet2

Logan

 

[kevin9999]

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
	A	B	C	D	E	F	G	H	I	J	K
1			Population Growth			Crime Severity Index			Unemployment Rate
2	City	Province	Total Change	Relative to total	Rating Equal to Other Factors	2021	Relative to total	Rating Equal to Other Factors	2021	Relative to total	Rating Equal to Other Factors
3	Red Deer	Alberta	0.4894	22.24815	1.77	176.39	0.079908	8.21	12.3	0.035777	3.58
4	Mississauga	Ontario	0.1714	63.53935	5.05	37.09	0.016802	1.73	14	0.040721	4.07
5	Brampton	Ontario	1.0173	10.70366	0.85	37.09	0.016802	1.73	13.3	0.038685	3.87
6	Edmonton	Alberta	0.5184	21.00318	1.67	97.45	0.044147	4.53	12.7	0.03694	3.69
7	Calgary	Alberta	0.4869	22.36329	1.78	72.27	0.03274	3.36	12.5	0.036358	3.64
8	Saskatoon	Saskatchewan	0.3523	30.9102	2.46	105.97	0.048006	4.93	8.7	0.025305	2.53
9	Saint John	New Brunswick			0.00	51.1	0.023149	2.38	10.9	0.031704	3.17
10	Burlington	Ontario	0.2394	45.48069	3.62	22.83	0.010342	1.06	10.7	0.031123	3.11
11	Oakville	Ontario	0.4769	22.83363	1.82	22.83	0.010342	1.06	11.7	0.034031	3.40
12	Regina	Saskatchewan	0.2703	40.27954	3.20	116.81	0.052917	5.43	9.9	0.028796	2.88
13	Toronto	Ontario	0.1261	86.36425	6.87	57.8	0.026185	2.69	13.9	0.04043	4.04
14	Markham	Ontario	0.6226	17.4884	1.39	57.8	0.026185	2.69	13.8	0.04014	4.01
15	Vaughan	Ontario	0.7751	14.04847	1.12	57.8	0.026185	2.69	12.5	0.036358	3.64
16	Etobicoke	Ontario			0.00	57.8	0.026185	2.69		0	0.00
17	Scarborough	Ontario			0.00	57.8	0.026185	2.69		0	0.00
18	Halifax	Nova Scotia	0.2247	48.4491	3.85	65.72	0.029772	3.06	11.4	0.033159	3.32
19	Oshawa	Ontario	0.2613	41.67335	3.31	41	0.01851	1.90	15.2	0.044212	4.42
20	Whitby	Ontario	0.5844	18.63077	1.48	41	0.01851	1.90	12.3	0.035777	3.58
21	Ajax	Ontario	0.7174	15.17717	1.21	40.86	0.01851	1.90	13.4	0.038976	3.90
22	St John's	Newfoundland & Labrador	0.1144	95.20912	7.57	67.86	0.030742	3.16		0	0.00
23	Hamilton	Ontario	0.1613	67.50144	5.37	66.31	0.03004	3.08	12.3	0.035777	3.58
24	Lethbridge	Alberta	0.4606	23.64047	1.88	128.65	0.058281	5.98	9.4	0.027341	2.73
25	St. Catherines-Niagara	Ontario	0.0591	184.2638	14.66	62.12	0.028142	2.89	15.8	0.045957	4.60
26	Winnipeg	Manitoba	0.2099	51.8671	4.13	113.55	0.05144	5.28	8.9	0.025887	2.59
27	Brandon	Manitoba	0.292	37.29009	2.97	112.65	0.051033	5.24	5.8	0.01687	1.69
28	London	Ontario	0.2549	42.7167	3.40	80.99	0.03669	3.77	13.3	0.038685	3.87
29	Fredericton	New Brunswick	0.3271	33.29029	2.65	75.5	0.034203	3.51	8.6	0.025015	2.50
30	Cambridge	Ontario	0.2547	42.75806	3.40	74.95	0.033954	3.49	11.5	0.03345	3.34
31	Kitchener	Ontario	0.3492	31.18229	2.48	74.95	0.033954	3.49	11	0.031995	3.20
32	Waterloo	Ontario	0.4032	27.00643	2.15	74.95	0.033954	3.49	12	0.034904	3.49
33	Pickering	Ontario	0.1383	78.76028	6.26		0	0.00	12.9	0.037522	3.75
34	Richmond Hill	Ontario	0.5301	20.53988	1.63		0	0.00	13.1	0.038104	3.81
35			10.8886	43.35239		2207.41	0.032258		343.8
Sheet1

Cell Formulas
Range	Formula
E3:E34,K3:K34,H3:H34	E3	=D3/(SUM(D$3:D$34))*100

 

[RoryA]

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:

Make Ratings Equal Weight Across Factors

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...

chandoo.org

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.