if statements, I think?

  • Thread starter Thread starter Stephen Doyle
  • Start date Start date
S

Stephen Doyle

When I mark students work I give them a percentage. I need a way to turn
that percentage into a C-C, A+etc.
E.g. if Cell is <55 but >60 = C
There are 9 sets of < and >, C- to A+
Is it possible to do this in Excel and if so how?

TIA
Stephen
 
Hi,

Yes, you want to do a If/Then statement. Go up to your
functions toolbar.

I personally would create a master key setting this
information up

Column 1 Column 2
100% A
90% B

From this point you can do a V-lookup.

Good luck - either way will do the trick.
 
Stephen,

you could do it directly like this:

=VLOOKUP(A1,{0\"F";50\"D";55\"D+";60\"C-";64\"C";67\"C+";70\"B-";73
\"B";7
6\"B+";80\"A-";85\"A";90\"A+"},2)

or create a table somewhere..

0 F
50 D
55 D+
60 C-
64 C
67 C+
70 B-
73 B
76 B+
80 A-
85 A
90 A+

and use..
=vlookup(a1,sheet2!$a$1:$b$12,2)


or with a named range..

=vlookup(a1,grades,2)

note that separators ARE international...
if pasting is a problem in example1 then do it like example 2, then
select the table ref in the formula bar and press f9 to convert to an
array of constants.. :)




keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >
 
One way is using VLOOKUP

To illustrate ..

In say, Sheet1, set-up a grade table in A2:B6
------------------
0% F
50% D
70% C
80% B
90% A

The above table presumes the grading criteria:

A: >=90%
B: >=80% to <90%
C: >=70% to <80%
D: >=50% to <70%
F: <50%

In Sheet2, assume the marks (in%) are in col A, A2 down
------------
Put in B2: =VLOOKUP(A2,Sheet1!$A$2:$B$6,2,TRUE)
Copy B2 down as many rows as there are data in col A

Col B will return the grades
according to the grade criteria specified in Sheet1
 
Hi Stephen

you can do it as an IF statement if you wanted to
assuming the following
A+ <90
A <80
A- <70
B+ <60
B <50
B- <40
C+ <30
C <20
C- <10
F eveything else

the following IF statement will work
=IF(A15>60,IF(A15>90,"A+",IF(A15>80,"A",IF(A15>70,"A-","B+"))),IF(A15<=50,IF
(A15<=40,IF(A15<=30,IF(A15<=20,IF(A15<=10,"F","C-"),"C"),"C+"),"B-"),"B"))

Edit it as appropriate for your range and values.

Cheers
JulieD
 
Hi

You didn't give which % correspond to which labels! And to which one do
belong values like 50%, or 60%, or 65%, ...

When
50% and <=55% is C-
55% and <=60% is C etc. with 5% step, until
90% is A+
Click to expand...

then with percentage in cell A2
=CHOOSE(INT(INT(20*(A2-0.01))-9-(INT(20*(A2-0.01))=19)),"C-","C","C+","B-","
B","B+","A-","A","A+")
or
=CHOOSE(INT(INT(20*(A2-0.01))-9-(INT(20*(A2-0.01))=19));"C-";"C";"C+";"B-";"
B";"B+";"A-";"A";"A+")
(which one, depends on your country settings)
 
Back
Top