Using "if" function to translate 100 scale into 4.0 scale
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 |
Using "if" function to translate 100 scale into 4.0 scale
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 "Robert McN" wrote: 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 |
Using "if" function to translate 100 scale into 4.0 scale
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}) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Robert McN" wrote in message ... 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 |
All times are GMT +1. The time now is 01:11 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com