Score calculation

[excelgal2016]

I've been trying to come up with a formula for a total - single score based on 4 variables. I'm afraid it's just a task too big for my old brain. It's perhaps not even a proper Excel question (I am using Excel to analyze the data though!).

I have 4 columns with a range of performance values for individual employees. I'd like to compare employee performance based on a single total score. Example:

Anyone can think of a formula how to calculate a single score from the 4 ratings? I'd very much appreciate the help.

 

[JamesCanale]

Are the ranges of all 4 ratings from 0-100? If so:

MrExcelPlayground6.xlsx
	A	B	C	D	E	F
19	Person	1	2	3	4	Total
20	One	1	100	80	33	86.5
21	Two	32	99	12	5	68.5
Sheet27

Cell Formulas
Range	Formula
F20:F21	F20	=AVERAGE(100-B20,C20,D20,100-E20)

The higher the better for the total.

 

[6StringJazzer]

Create rows assigning numbers to the "___er the better" idea. Show the worst and best number for each rating.

Then use that to calculate a "% good" for each rating, and add those up, assuming they have equal weights. If they do not have equal weights, then you need yet another row of numbers giving the weights.

Are all the numbers on a scale of 0-100? If not what are they?

I also suggest updating your profile to show us your version of Excel. Excel 365 has features that might be useful for this problem.

This solution determines a percentage score for each rating, assuming the ranges I showed, and then adds the percentages for a total point score (0 is worst, 400 is best)

$scratch.xlsm
	A	B	C	D	E	F
1		Rating 1	Rating 2	Rating 3	Rating 4	Total Score
2	Employee 1	1	100	80	33	315
3	Employee 2	32	99	12	5	240
4
5
6	Weights
7	Best	1	100	100	1
8	Worst	50	0	0	50
excelgal2016

Cell Formulas
Range	Formula
F2:F3	F2	=100*SUMPRODUCT((B2:E2-$B$8:$E$8)/($B$7:$E$7-$B$8:$E$8))

 

[excelgal2016]

@JamesCanale your profile picture just caused me a sudden onset of hunger!

Yes sorry... an important piece of information I missed there.

Rating 1 - no max - can be anything; min value = 1
Rating 2 - no max - can be anything; min value = 1
Rating 3 - no max - can be anything; min value = 1
Rating 4 - max value = 100, min value = 1

Thank you for your reply

 

[JamesCanale]

I went logarithmic.

MrExcelPlayground6.xlsx
	A	B	C	D	E	F
19	Person	1	2	3	4	Total
20	One	1	100	80	33	83.85
21	Two	32	99	12	5	68.71
Sheet27

Cell Formulas
Range	Formula
F20:F21	F20	=SUM(3-LOG10(B20),LOG10(C20),LOG10(D20),3-LOG10(E20))*10

Through in some edge cases and see if there are sensible relative scores.

 

[excelgal2016]

@6StringJazzer @JamesCanale Thank you both for your responses and the solutions. I will be trying both tomorrow.

I have also updated my profile. My version is 2019.

 

[excelgal2016]

Couldn't help it and tried it now. Since I don't have top range for 2 of the variables (top is open, it can be anything), James's solution works better in this case, so I'll mark it as a solution. I'm definitely saving both, I'm sure I'll find a use for it. Your help is very much appreciated!