Assigning a point value to a range of numbers
 

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?

Assigning a point value to a range of numbers
 

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

--
Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips


"wmb" wrote:

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?

Assigning a point value to a range of numbers
 

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.


--
John C


"wmb" wrote:

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?