Help with formulae

[Philip Atherton]

I am trying to work out the formula for the following:

In two columns I have eight 20 assignments with scores added to each
assignment).

I then want to automatically grade these scores for each assignment where a
grade will have a different range of values. If the score falls into one of
the 12 grades, I want the cell to count one.

I've tried IF statements, LOOKUP but doesn't seem to work. Any help would be
appreciate. Thank you.

 

[Jonah]

Try COUNTIF(Az:Fy,"score")
J

 

[Max]

Assume the "Assignment - Score" table below is
in Sheet1, cols A and B, data from row2 down:

Ass Sc
111 4
112 10
113 20
etc

(Ass = Assignment, Sc = score)

Assume your "grade" table is in Sheet2,
in A1: C13, data from row2 down, viz.:

L U Gr
0 3 X
7 12 Y
15 20 Z
45 55 G
75 85 P
etc

(L = lower, U = Upper, Gr = Grade)

where the 12 score ranges - grade buckets need not necessarily be continuous
nor sorted. There could be gaps or perhaps even some overlaps between the
various score ranges. The alpha grades: X, Y, Z ... are actually not
relevant here.

In Sheet1
-----------
Put in C2 and array-enter (press CTRL+SHIFT+ENTER):

=IF(B2="","",IF(ISNUMBER(MATCH(1,(B2>=Sheet2!$A$2:$A$13)*(B2<=Sheet2!$B$2:$B
$13),0)),1,0))

Copy C2 down
(can copy ahead of expected data input in col B)

Col C will return "1"'s where the score in col B falls within any one score
range - grade bucket in Sheet2, "0"s otherwise, except for empty cells in
col B which will return blanks: "'.

Adapt to suit ..