Formula for calculating scores based on percentage

[JV0710]

Good Day

Please can I get some help for the following

I am trying to calculate scores for percentages achieved. My Scale is as follows

% Achieved Score
<95% : 1
95.00% : 1
95.50% : 1.2
96.00% : 1.4
96.50% : 1.6
97.00% : 1.8
97.50% : 2
98.00% : 2.2
98.50% : 2.4
99.00% : 2.6
99.50% : 2.8
100.00% : 3
100.50% : 3.2
101.00% : 3.4
101.50% : 3.6
102.00% : 3.8
102.50% : 4
103.00% : 4.2
103.50% : 4.4
104.00% : 4.6
104.50% : 4.8
105.00% : 5
>105% : 5

From 95% every 0.5% increases the score by 0.2, so 95% = score of 1, 95.5% = score of 1.2 etc.
<95% gets a score of 1 and >105% gets a score of 5

Say - In cell A2, I have the percentage achieved, then,
in cell B2, I would like to have a formula that will calculate the score based on the % Achieved - catering for percentages that are not on the table above
For example a percentage achieved of 101.78 % would return a score of . . . . . ? (somewhere between 3.6 and 3.8)

Thanks in advance for the help

Joe

 

[Dave Patton]

T202212a.xlsm
	A	B	C
1	95.50%	1.200
2	101.78%	3.712
3	99.75%	2.900
4	106.00%	5.000
5
6		% Achieved Score
7		0.0%	1.0
8		95.0%	1.0
9		95.5%	1.2
10		96.0%	1.4
11		96.5%	1.6
12		97.0%	1.8
13		97.5%	2.0
14		98.0%	2.2
15		98.5%	2.4
16		99.0%	2.6
17		99.5%	2.8
18		100.0%	3.0
19		100.5%	3.2
20		101.0%	3.4
21		101.5%	3.6
22		102.0%	3.8
23		102.5%	4.0
24		103.0%	4.2
25		103.5%	4.4
26		104.0%	4.6
27		104.5%	4.8
28		105.0%	5.0
1e

Cell Formulas
Range	Formula
B1:B3	B1	=LOOKUP(A1,$B$7:$C$28)+(A1-VLOOKUP(A1,$B$7:$B$28,1,1))/0.005*0.2
B4	B4	=MIN(5,LOOKUP(A4,$B$7:$C$28)+(A4-VLOOKUP(A4,$B$7:$B$28,1,1))/0.005*0.2)

 

[Dave Patton]

T202212a.xlsm
	A	B	C
1	95.50%	1.200
2	101.78%	3.712
3	99.75%	2.900
4	1.06%	5.000
5
6		% Achieved Score
7		0.0%	1.0
8		95.0%	1.0
9		95.5%	1.2
10		96.0%	1.4
11		96.5%	1.6
12		97.0%	1.8
13		97.5%	2.0
14		98.0%	2.2
15		98.5%	2.4
16		99.0%	2.6
17		99.5%	2.8
18		100.0%	3.0
19		100.5%	3.2
20		101.0%	3.4
21		101.5%	3.6
22		102.0%	3.8
23		102.5%	4.0
24		103.0%	4.2
25		103.5%	4.4
26		104.0%	4.6
27		104.5%	4.8
28		105.0%	5.0
1e

Cell Formulas
Range	Formula
B1:B3	B1	=LOOKUP(A1,$B$7:$C$30)+(A1-VLOOKUP(A1,$B$7:$B$28,1,1))/0.005*0.2
B4	B4	=MAX(LOOKUP(A4,$B$7:$C$30)+(A4-VLOOKUP(A4,$B$7:$B$28,1,1))/0.005*0.2,5)

 

[Dave Patton]

sorry for the multiple posts. I had trouble with internet connections.

T202212a.xlsm
	A	B
1	95.50%	1.200
2	101.78%	3.712
3	99.75%	2.900
4	106.00%	5.000
1e

Cell Formulas
Range	Formula
B1:B4	B1	=MIN(5,LOOKUP(A1,$B$7:$C$28)+(A1-VLOOKUP(A1,$B$7:$B$28,1,1))/0.005*0.2)

 

[Dave Patton]

The coffee may have helped.
Review the logic in the following, test, and use your preference.

T202212a.xlsm
	E
1	1.000
2	3.712
3	2.900
4	5.000
5
1e

Cell Formulas
Range	Formula
E1:E4	E1	=MIN(5,(A1-Small)/0.005*0.2+1)

Named Ranges
Name	Refers To	Cells
Small	='1e'!$A$1	E1:E4

 

[Dave Patton]

T202212a.xlsm
	A	B	C	D	E
1	95.00%	1.000	1.000	1.000	1.000
2	101.78%	3.712	3.712	3.712	3.712
3	99.75%	2.900	2.900	2.900	2.900
4	106.00%	5.000	5.000	5.000	5.000
5
1e

Cell Formulas
Range	Formula
B1:B4	B1	=MIN(5,LOOKUP(A1,$B$7:$C$28)+(A1-VLOOKUP(A1,$B$7:$B$28,1,1))/0.005*0.2)
C1:C4	C1	=MIN(5,LOOKUP(A1,$B$7:$C$28)+(A1-FLOOR(A1,0.005))/0.005*0.2)
D1:D4	D1	=MIN(5,LOOKUP(A1,$B$7:$C$28)+(A1-FLOOR(A1,0.005))*40)
E1:E4	E1	=MIN(5,(A1-Small)/0.005*0.2+1)

Named Ranges
Name	Refers To	Cells
Small	='1e'!$A$1	B1:D1, E1:E4

 

[JV0710]

Good Morning

Thanks very much for your help Dave
These options have helped and solved my problem

 

[Dave Patton]

Thanks for the feedback.