Using "if" function to translate 100 scale into 4.0 scale

[Guest]

I'm using MS Excel 2003 in Windows XP. I teach at a university in which
final grades must be submitted (today!) and they are on a 4.0 scale. My
students were graded on a 100 point scale. I'd have my grades in Excel and
I'd like to use a function such that if the final grade falls between 95-100
the student gets a 4.0, between 90-94 a 3.7, etc. I know how to use the "if"
function, but that would only allow me for two possible outcomes, whereas, I
need to have a range of outcomes from .7 to 4.0. (The increments are 1.0,
1.3, 1.7, 2.0, etc.) Any suggestions as to how to do this?
Thanks very much,
Bob

 

[Guest]

put the lowest numeric value for each grade range in a column . in the
column to the right put the next grade

85 3.5
90 3.7
95 4.0

then you can use a VLOOKUP(numeric grade, 2-col range described above, 2) to
convert from 100 scale to 4.0 scale

 

[Ragdyer]

You say your lowest grade is 0.7.

Does that mean that a "no show", or zero score still returns a 0.7 grade?

If so, try this formula:

=LOOKUP(A1,{0,50,55,60,65,70,75,80,85,90,95;0.7,1,1.3,1.7,2,2.3,2.7,3,3.3,3.
7,4})