Points And Percentages

[Saxman]

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

--

 

[Guest]

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

 

[Gord Dibben]

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

 

[Saxman]

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.
--

 

[Gord Dibben]

Based on your sequence, it looked like a typo so allowed for that without
confusion.

Thanks for the feedback.