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 ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Philip Atherton" wrote in message
...
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.
|