Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Paste Formulae | Excel Discussion (Misc queries) | |||
repeat formulae taking into account previous column's info? | Excel Discussion (Misc queries) | |||
Formulae, conditional formatting & macro security | Excel Discussion (Misc queries) | |||
Excel and CubeCell Formulae | Excel Discussion (Misc queries) | |||
vlookup change column index position - without changing formulae | Excel Worksheet Functions |