Scoring table

[willstone]

Hello All,
I am new to the forum, and an amateur with Excel.

I would like create a formula for scoring a range of values. The table is below
Score.... Range
-2....... 31 and below
0........ 32-37
1........ 38
2........ 39-42
3....... 43-44
4....... 45-47
5....... 48-49
6....... 50-51
7....... 52-53
8....... 54-55
9....... 56-57
10..... 58 and over

So I want to be able to put values in a column and have it show a score in a different column.
Thanks,
Will

[Pete_UK]

Set up a two column table like this:

0......-2
32.....0
38.....1
39.....2
43.....3
45.....4
48.....5
50.....6
52.....7
54.....8
56.....9
58.....10

Suppose you put this in cells S1:T12. Then use A1 for your value and put this formula in B1:

=IF(A1="","",VLOOKUP(A1,$S$1:$T$12,2))

copy down if you have more values in column A.

Hope this helps.

Pete

[willstone]

OK that worked great!
Now I want to add columns with same scoring format for seconds, but am having trouble getting the formula to work. I have looked at several websites on how to format minutes and seconds and it is not easy in Excel.Scoring table.xlsx
See below
-2..... 55.1 and over
0...... 55.0-52.5
1...... 52.4-51.1
2...... 51.0-49.5
3...... 49.4-48.0
4...... 47.9-46.1
5...... 46.0-45.0
6...... 44.9-44.0
7...... 43.9-43.0
8...... 42.9-42.0
9...... 41.9-41.0
10.... 40.9 and below

[Pete_UK]

Change the table in P1:Q12 to this:

0......10
41......9
42......8
43......7
44......6
45......5
46.1....4
48......3
49.5....2
51.1......1
52.5....0
55.1.....-2

i.e. the table is now sorted in order of increasing seconds (which is how the VLOOKUP function works when the 4th parameter is missing). Then you can change the formula in J2 to this:

=IF(I2="","",VLOOKUP(I2*60*60*24,$P$1:$Q$12,2))

Note that the *60*60*24 converts the time format into actual seconds. Then you can copy the formula down.

Hope this helps.

Pete

[willstone]

Thanks for your help that worked.
Last question how do I format minutes and seconds to do the same kind of scoring? I attached my file again. I tried to use a similar format as with the seconds, but can't get it to work.Scoring table rev1.xlsx

[Pete_UK]

I've been away for a few days, so apologies for the delay in responding.

I think you need to change the formula in H2 to this:

=IF(F2="","",VLOOKUP(F2,$M$1:$O$12,3))

then copy down.

Hope this helps.

Pete

[willstone]

I guess they don't call you the excel guru for nothing! Thank you very much!

Will

[Pete_UK]

I'm not the only Guru - there are about 20 or 30 who contribute regularly.

Glad to be of help.

Pete