Help with formulae
 

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.

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


Philip Atherton wrote:

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.

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.