One way:

=MIN(3,4-(CEILING(ABS(1-A1)*10,1)))


another:

=LOOKUP(ABS(1-A1),{0,3;0.1,2;0.2,1;0.3,0})


In article ,
"Derek" wrote:

With my lacklustre Excel skills, I am struggling to build a table that allows
me to score a number based on its location in a set of ranges. For example, I
want to score any percentage between 90% and 110% with a 3, anything between
80% and 90%/ 110% and 120% with a 2, and so on. Any ideas on how I can do
this easily?