compare two columns, then calculate a result

[dazp1970]

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!

 

[Peo Sjoblom]

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),VLOOKUP(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]

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>$AO$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 are:
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.

 

[Ken Johnson]

Hi Darrell,
This is the coward's way out.
1. Give every student an H grade in column AD using

=IF(AC3="","",IF(AC3>$AO$12,"A",IF(AC3>$AO$13,"B",IF(AC3>$AO$14,"C",IF(AC3>$AO$15,"D","U")))))

2. Give every student an F grade in column AE using

=IF(AC3="","",IF(AC3>$AP$12,"A",IF(AC3>$AP$13,"B",IF(AC3>$AP$14,"C",IF(AC3>$AP$15,"D",IF(AC3>$AP$16,"G","U"))))))

3. In column AF use =IF(AA3="H",AD3,AE3) to show the appropriate grade
depending on the students H/F status.

4. You can hide columns AD and AE

NB I've used 79 in AO12, 64 in AO13, 49 in AO14 and 34 in AO15 for the
H grades;
80 in AP12, 70 in AP13, 60 in AP14, 50 in AP15 and 40 in AP16 for the F
grades.

I'm getting kicked off this computer I'll continue later.

Ken Johnson

 

[Ken Johnson]

HiDarrell,

I just want to reiterate why I've used those numbers in AO13:AO15 for
the Higher grades and in AP13:AP16 for the Foundation grades. These
numbers represent the top possible score for each grade ie 79 is the
top possible score for a B (H) therefore the formula first checks to
see if the mark is greater than 79 (AO12 = 79), which would result in
an A (H). If this is not the case then the formula checks to see if the
mark is greater than 64 (AO13=64), which would result in a B (H), etc.

I teach Science to adolescents in a Sydney (Australia) State High
School and I use Excel all the time. Your problem is a little more
complicated than usual. I guess you could achieve the same result with
one formula in one column rather than using three columns like I'm
suggesting, but its just after midnight here and my brain's slowing
right down. I'll look into it another time.

Hope you find this useful.

Ken Johnson

 

[dazp1970]

Done it!

I tried Peo's method - I couldn't figure it out at first, but I went through
the formula to see what was happening, made a few adjustments and now it
works a treat!

Thanks to both of you for your help.

Darrell.
PS: don't suppose either of you know anything about conditional formatting?
I may have to add another post if I can't work it out!

 

[Norman Jones]

Hi Darrell,

PS: don't suppose either of you know anything about conditional
formatting?
I may have to add another post if I can't work it out!

See Debra Dalgleish's Conditional Formatting tutorial at:

http://www.contextures.com/xlCondFormat01.html

 

[Ken Johnson]

Hi Darrell,
I guess it doesn't matter now since you've got it working, but I just
noticed I stuffed up the cut-offs for the Foundation grades. They
should have been 79,69,59 49 and 39.
Call me a pillock!
Ken johnson

 

[Ken Johnson]

Hi Darrell,
I bet you want to use a different cell color for each of the grades. I
think you've got too many grades to be able to use conditional
formatting which is limited to something like four different colors,
not enough.
Ken Johnson

 

[Norman Jones]

Hi Darrell,

And if Ken is correct in surmising that you needs may exceed the current
Conditional Format limits, see xlDynamic at:

http://www.xldynamic.com/source/xld.CFPlus.Download.html