Assigning a point value to a range of numbers

[wmb]

I am creating a spreadsheet where I need to assign a point value to a range
of numbers. For instance, if a person was 100-102% of there sales target,
they would earn 5 points. If they were 98-99.9%, they earn 4 points. How
would I set up a formula to populate the points by just typing in the % they
were vs. their sales target?

 

[M Kan]

Use a VLOOKUP without exact match. Create a table with the lower thresholds
of each point band and the associated points and that should help. Here's an
example (page down once)

http://www.kan.org/tips/scorecard.php

 

[John C]

If you just have a point range from 1-5, you could do something like this (I
don't know your baseline for 1, 2, or 3 points, so I have guessed.
C2: Sales Target (in percentage)
D2: =(C2>=92%)+(C2>=94%)+(C2>=96%)+(C2>=98%)+(C2>=100%)

Of course, if your points are evenly spaced as in my example above, you
could also modify the formula like this:
=MAX(MIN(INT(((C2-92%)*50))+1,5),0)
Ensure the cells are formatted to General (or number).

Hope this helps.