Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Points And Percentages
I have the following list for which I award points.
60 to 100 = 25 points 50 to 59 = 20 40 to 49 = 12 30 to 39 = 9 20 to 29 = 5 15 to 29 = 3 10 to 14 = 2 1 to 9 = 1 0 = 0 How can I best represent the data above wherby if any value entered in cell A1 from 0 to 100 is modified in cell B1 to represent the points above. Is this possible? TIA -- |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Points And Percentages
Create a table (say in Sheet2, column A & B) as below:
A B 0 0 1 1 10 2 15 3 20 5 30 9 40 12 50 20 60 25 In Sheet B1: =VLOOKUP(A1,Sheet2!$A$1:$B$9,2,-1) A1 has your value 0 to 100 HTH "Saxman" wrote: I have the following list for which I award points. 60 to 100 = 25 points 50 to 59 = 20 40 to 49 = 12 30 to 39 = 9 20 to 29 = 5 15 to 29 = 3 10 to 14 = 2 1 to 9 = 1 0 = 0 How can I best represent the data above wherby if any value entered in cell A1 from 0 to 100 is modified in cell B1 to represent the points above. Is this possible? TIA -- |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Points And Percentages
Assuming 15-29 is a typo and you meant 15-19
Enter this formula in B1 =LOOKUP(A1,{0,1,10,15,20,30,40,50,60,100},{0,1,2,3 ,5,9,12,20,25,"over 100"}) Gord Dibben MS Excel MVP On Sat, 30 Jun 2007 10:41:08 -0500, "Saxman" wrote: I have the following list for which I award points. 60 to 100 = 25 points 50 to 59 = 20 40 to 49 = 12 30 to 39 = 9 20 to 29 = 5 15 to 29 = 3 10 to 14 = 2 1 to 9 = 1 0 = 0 How can I best represent the data above wherby if any value entered in cell A1 from 0 to 100 is modified in cell B1 to represent the points above. Is this possible? TIA |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Points And Percentages
Gord Dibben wrote:
Assuming 15-29 is a typo and you meant 15-19 I did, sorry for the confusion. Enter this formula in B1 =LOOKUP(A1,{0,1,10,15,20,30,40,50,60,100},{0,1,2,3 ,5,9,12,20,25,"over 100"}) This works a treat. Thank you very much. -- |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Points And Percentages
Based on your sequence, it looked like a typo so allowed for that without
confusion. Thanks for the feedback. On Sat, 30 Jun 2007 13:42:45 -0500, "Saxman" wrote: Gord Dibben wrote: Assuming 15-29 is a typo and you meant 15-19 I did, sorry for the confusion. Enter this formula in B1 =LOOKUP(A1,{0,1,10,15,20,30,40,50,60,100},{0,1,2,3 ,5,9,12,20,25,"over 100"}) This works a treat. Thank you very much. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I find points on a curve between known points? | Excel Worksheet Functions | |||
PERCENTAGES | Excel Worksheet Functions | |||
Percentages | Excel Worksheet Functions | |||
Percentages | Excel Discussion (Misc queries) | |||
Percentages | Excel Discussion (Misc queries) |