Which formula to use to calculate ratings

Good day,

I'm looking for a formula to use. I need to calculate ratings on variances in percentages:

Variance vs expected Rating
0% 3
1-5% 2.5
6-10% 2
10-15% 1.5
15%+ 1

The expected percentage is 5. In other words if a % below 5 is obtained the rating is 3. Between 6% and 10% the rating should be 2.5.

Thanks

Lucille

Try this:

Data Range

	A	B	C	D	E	F
1	Lookup Table				Analysis Results
2	0.0%	3			4.90%	2.5
3	1.0%	2.5			8.50%	2
4	5.0%	2			3.20%	2.5
5	10.0%	1.5			12.60%	1.5
6	15.0%	1			18.00%	1
7
8
9	Formula in F2 and copied down
10	VLOOKUP(E2,$A$2:$B$6,2,TRUE)

0% 3
1-5% 2.5
6-10% 2
10-15% 1.5
15%+ 1
those 2 ranges over lap

so does 10-15 and 15%+
so assuming you mean
0
1-5
6-9
10-14
15 and over
=LOOKUP(A1,{0,0.01,0.06,0.1,0.15},{3,2.5,2,1.5,1})

See attached

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.