Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Assigning point values? | Excel Worksheet Functions | |||
Assigning Variable Range | Excel Discussion (Misc queries) | |||
Assigning a value to a set of numbers | Excel Worksheet Functions | |||
Assigning numbers to names | Excel Discussion (Misc queries) | |||
Assigning scores to a date range | Excel Worksheet Functions |