converting numbers

[sevy]

I need to convert practice time totals to number grades. Anything from 0 to
65 needs to stay as is, and 66 to 180 minutes need to convert to a range of
grades from 65 to 100. For example, a practice time of 180 minutes should
return 100 as a grade. I know about vlookup, and can get specific cutoffs
such as 180 minutes = 100, 150 minutes = 95, 120 minutes = 85, 90 minutes =
75, and 65 minutes = 65. How do I get the numbers that would fall in between
without having to do an individual lookup for each possible number?

 

[T. Valko]

If I understand you...

0 to 65 = 0 to 65
66 to 90 = 75
91 to 120 = 85
121 to 150 = 95
150+ = 100

A1 = some number

=IF(A1<=65,A1,LOOKUP(A1,{66,91,121,151},{75,85,95,100}))

 

[Sheeloo]

Use TRUE as the last parameter of your VLOOKUP - it gives you the closest
match.

Enter lower ranges in one column and grades against that in another column...
Sort ascending and then enter VLOOKUP

For your values;
1. Enter in Col A
65
90
120
150
180

2. Enter in Col B
65
75
85
95
100

3. Enter the following formula in your grade column (assuming value to grade
are in Col L)

=IF(L1>65,VLOOKUP(L1,A:B,2,TRUE),L1)

 

[Bernie Deitrick]

sevy,

You need to do tabular interpolation.

Enter this table in A1:B7 (A1 and B1 have the headers)

Time Score
0 0
65 65
90 75
120 85
150 95
180 100

Enter the actual minutes in cell D2, and enter this into cell E2

=IF(ISERROR(MATCH(D2,$A$2:$A$7,FALSE)),TREND(OFFSET($B$1,MATCH(D2,$A$2:$A$7,1),0,2,1),OFFSET($A$1,MATCH(D2,$A$2:$A$7,1),0,2,1),D2),INDEX(B2:B7,MATCH(D2,$A$2:$A$7,FALSE)))

HTH,
Bernie
MS Excel MVP

 

[Rick Rothstein]

If you are willing to forego grades rounded to the even 5's, here is the
formula that results from creating an exact proportional scaling between
your minutes range and your grades range (with a roundup to an integer value
to eliminate the decimal portions)...

=IF(A1<65,A1,ROUNDUP((35*A1+5065)/114,0))

Your milestone grades come out like this... 65=>65, 90=>73, 120=>82,
150=>91, 180=>100.

 

[Lori]

=PERCENTILE({0,65,75,85,95,100},PERCENTRANK({0,65,90,120,150,180},A1,30))