Peo,

thanks for taking the time to post. The two different sets of grades are
located in a separate part of the sheet. $AO$12, and $AO$13 and . . . so on
are the cells where the pass marks are stored. AO12 is 80%, AO13 is 70% and
so on down the grades. I have done this so that I can simply edit the pass
mark. For example, if I decide that to get an A you need to get over 85% I
can just go and change the value in cell AO12.

The IF formula that determines the grade is currently (in cell AD3):
=IF(AC3$AO$12,"A",IF(AC3$AO$13,"B",IF(AC3$AO$14 ,"C",IF(AC3$AO$15,"D",IF(AC3$AO$16,"E",IF(AC3$A O$17,"F",IF(AC3$AO$18,"G",IF(AC3<$AO$18,"U")))))) ))

AA3 currently has the letter H or F in it to determine Higher or Foundation
student.
AB3 currently has the raw mark (out of 60). The percentage is calculated in
AC3 from this raw mark.
AC3 is the cell with the result (as a percentage).
AD3 has the IF formula above - Excel goes to look at the values in the AO
column, compares the result, and returns the appropriate grade.

This IF formula will need editing because the pass marks a
Higer paper - A (80% or more), B (65-79%), C (50-64%), D (35-49%), U (34% or
less).
Foundation paper - C (80% or more), D (70-79%), E (60-69%), F (50-59%), G
(40-49%), U (39% or less).

Ideally I would like to have two separate areas on the sheet where the %
needed for a particular grade is stored (one area for the Higher students and
one for the Foundation students).

My aim was to get Excel to . . . (as an example)
see what letter is stored in AA3 (example "H")
see what is the result in AC3 (example "55")
compare the result with the correct set of pass marks (example (for higher
papers) "C")

Sorry to have gone on and on, any ideas would be greatly appreciated!

Darrell.



"Peo Sjoblom" wrote:

So what are the 2 different sets of grades for H and F

here's a way to do it, assume that the first paper is in B3 and the score in
C3

=IF(B3="","",IF(B3="F",VLOOKUP(C3,{0,"G";0.45,"F"; 0.55,"E";0.65,"D";0.75,"C";0.85,"B";0.95,"A"},2),V LOOKUP(C3,{0,"G";0.45,"F";0.55,"C";0.65,"D";0.75," C";0.85,"B";0.95,"A"},2)))

the vlookup tables are identical with the exception of 55%, first is for F
second for H, replace the grades/scores with what you need

--
Regards,

Peo Sjoblom

(No private emails please)


"dazp1970" wrote in message
...
I am teacher and I have a sheet set up to record student test results. I
type in the score (out of 60) and the sheet calculates a percentage. I
have
then entered an IF function in the next column to convert this pergentage
into a grade (a letter grade from A to G). My difficulty is in the next
stage
. . .

Some students are going to sit a higer paper (H) and some a foundation
paper
(F). I have a column where H or F is entered in order to record this
information. The papers however have different pass marks (e.g. 55% in a
Higher paper gets a "C" but only gets a "E" in the Foundation paper).

What I want to do (unsucessfully at the moment!) is get Excel to look to
see
if there's an H or F in the first column, then perform an IF calculation
(or
maybe some other function?) to convert the score into a grade relevant for
that paper.

Any help on this would be really appreciated. I have tried lots of IFs,
ANDs
and other functions without success - it's driving me nuts!