Value in evaluations

[stevenlee]

Hello Excel Wizards,

I probably titled the question wrong and will do a terrible job at explaining what I want. Sorry in advanced!

I've put together a evaluation sheet for baseball tryouts and need to figure out the best way to average scores with different weights in each category. For example:

I have 6 categories:
1) Hitting
-Contact (no/yes) (0/1)
-Hit (no/yes) (0/1)
-Swing (Good, great, perfect) (1/2/3)
-Power (Good, great, perfect) (1/2/3)
-Extra Bases/Crushed (Good, great, perfect) (1/2/3)
2) Popflys
3) Fielding
4) Speed
5) Pitching
6) Intangibles

Questions:
What is the best way to average out the scores for each category? (i.e how to i find a consistent average across all categories)
What is the best way to weigh on category more than others (i.e I want to put a premium on pitching vs hitting but intangibles higher than pitching)

Thanks!

 

[Sam_D_Ben]

Something like this,

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Number	League Age -	Pitcher/Catcher? -	Skill Avg -	Avg w/Intag.	Hitting .	Popflys Y	Fielding Y	Speed -	Pitching -	Intangibles .		Skill Avg -	Avg w/Intag.		Skill Avg -	Avg w/Intag.
2	1	10	C Only	5.13	5.94	3.75	4	6.4	1.5	10	10		5.66	6.82		6.02	7.19
3	2	10	P & C	8.03	8.64	7.5	8	10	3	11.67	11.67		8.44	9.30		8.71	9.58
4	3	11	P & C	8.05	8.93	9	9	7.4	1.5	13.33	13.33		8.47	9.76		8.87	10.18
5	4	11	P Only	7.42	8.68	5	7.6	8	1.5	15	15		8.12	9.96		8.70	10.55
6	5	12	C Only	7.8	8.17	8.5	10	8	2.5	10	10		7.93	8.48		8.10	8.66
7	6	12	P & C	6.25	7.16	4	8	6.6	1	11.67	11.67		6.70	8.02		7.11	8.45
8	7	12	P & C	9.07	9.78	10	9	10	3	13.33	13.33		9.49	10.51		9.81	10.85
9	8	12	P Only	6.52	7.93	3.5	4.6	8	1.5	15	15		7.41	9.44		8.05	10.09
Sheet1

Cell Formulas
Range	Formula
M2:M9	M2	=(F2*1 + G2*0.8 + H2*1.2 + I2*1 + J2*1.5) / (1 + 0.8 + 1.2 + 1 + 1.5)
N2:N9	N2	=(F2*1 + G2*0.8 + H2*1.2 + I2*1 + J2*1.5 + K2*2) / (1 + 0.8 + 1.2 + 1 + 1.5 + 2)
P2:P9	P2	=(F2*1 + G2*0.8 + H2*1.2 + I2*1 + J2*2) / (1 + 0.8 + 1.2 + 1 + 2)
Q2:Q9	Q2	=(F2*1 + G2*0.8 + H2*1.2 + I2*1 + J2*2 + K2*2.5) / (1 + 0.8 + 1.2 + 1 + 2 + 2.5)

 

[stevenlee]

Something like this,

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Number	League Age -	Pitcher/Catcher? -	Skill Avg -	Avg w/Intag.	Hitting .	Popflys Y	Fielding Y	Speed -	Pitching -	Intangibles .		Skill Avg -	Avg w/Intag.		Skill Avg -	Avg w/Intag.
2	1	10	C Only	5.13	5.94	3.75	4	6.4	1.5	10	10		5.66	6.82		6.02	7.19
3	2	10	P & C	8.03	8.64	7.5	8	10	3	11.67	11.67		8.44	9.30		8.71	9.58
4	3	11	P & C	8.05	8.93	9	9	7.4	1.5	13.33	13.33		8.47	9.76		8.87	10.18
5	4	11	P Only	7.42	8.68	5	7.6	8	1.5	15	15		8.12	9.96		8.70	10.55
6	5	12	C Only	7.8	8.17	8.5	10	8	2.5	10	10		7.93	8.48		8.10	8.66
7	6	12	P & C	6.25	7.16	4	8	6.6	1	11.67	11.67		6.70	8.02		7.11	8.45
8	7	12	P & C	9.07	9.78	10	9	10	3	13.33	13.33		9.49	10.51		9.81	10.85
9	8	12	P Only	6.52	7.93	3.5	4.6	8	1.5	15	15		7.41	9.44		8.05	10.09
Sheet1

Cell Formulas
Range	Formula
M2:M9	M2	=(F2*1 + G2*0.8 + H2*1.2 + I2*1 + J2*1.5) / (1 + 0.8 + 1.2 + 1 + 1.5)
N2:N9	N2	=(F2*1 + G2*0.8 + H2*1.2 + I2*1 + J2*1.5 + K2*2) / (1 + 0.8 + 1.2 + 1 + 1.5 + 2)
P2:P9	P2	=(F2*1 + G2*0.8 + H2*1.2 + I2*1 + J2*2) / (1 + 0.8 + 1.2 + 1 + 2)
Q2:Q9	Q2	=(F2*1 + G2*0.8 + H2*1.2 + I2*1 + J2*2 + K2*2.5) / (1 + 0.8 + 1.2 + 1 + 2 + 2.5)

Thank you!