A
Arnold
Hi All,
I would like for teachers to be able to enter point values (0, 20, 78,
99, etc.) AND letter grades: A+, B-, C, as well as selected other
letters-I for incomplete, N for no credit, and X for excused-in the
same cells and have Excel use both the point values and letter grades
in formulas.
If teachers enter an A- or a C+ into a cell, is there a way for Excel
to treat that like the average or midpoint of the A- or C+ range? The
A- range is from 90 to 93.33, and the C+ range is 76.66 to 79.99. So,
an A- would equal 91.67, and a C+ would = 78.33.
===============================
Here's more info on what I have right now...
There are columns in which numeric point values only are entered for
assignment scores. For instance:
Student X AA13 = 20
Student Y AA14 = 17
Student Z AA15 = <blank> because the student had an excused
absence
In cell AA10, the max point value of 20 was entered for that
assignment. Assignments were added to the right of col. AA almost
daily. >>Note that if a teacher used letter grades instead of point
values, the max values in row 10 would not be needed<<
Scores were averaged across a quarter. So, the following formulas
calculated the points possible, average score, and a % based off of
assignment points:
For Student X...
P13 = SUMIF(AA13:AX13,">0",AA$10:AX$10)
Q13 =SUM(AA13:AX13)
R13 =AVERAGE(IF(($AA13:AX13<>"")*($AA$10:AX$10<>0),$AA13:AX13/$AA
$10:AX$10))*100
In determining final letter grades, I did some weighting with some
other scores, but ultimately used this formula in col. X:
=IF(V13<>"",HLOOKUP(V13/100,Hgrades,2),"")
So, there were 2 cols. in a sheet named Fields that the HLOOKUP
referenced:
0 N
40 I
50 F
60 D-
63.33 D
66.67 D+
70 C-
73.33 C
76.67 C+ (In the above, a C+ would = 78.33)
80 B- (In the above, a B- would = 71.67)
83.33 B (In the above, a B would = 85.00)
86.67 B+ (In the above, a B+ would = 88.33)
90 A- (In the above, an A- would equal 91.67)
93.33 A
96.67 A+
===============================
Very curious...Thanks a lot!
Arnold
I would like for teachers to be able to enter point values (0, 20, 78,
99, etc.) AND letter grades: A+, B-, C, as well as selected other
letters-I for incomplete, N for no credit, and X for excused-in the
same cells and have Excel use both the point values and letter grades
in formulas.
If teachers enter an A- or a C+ into a cell, is there a way for Excel
to treat that like the average or midpoint of the A- or C+ range? The
A- range is from 90 to 93.33, and the C+ range is 76.66 to 79.99. So,
an A- would equal 91.67, and a C+ would = 78.33.
===============================
Here's more info on what I have right now...
There are columns in which numeric point values only are entered for
assignment scores. For instance:
Student X AA13 = 20
Student Y AA14 = 17
Student Z AA15 = <blank> because the student had an excused
absence
In cell AA10, the max point value of 20 was entered for that
assignment. Assignments were added to the right of col. AA almost
daily. >>Note that if a teacher used letter grades instead of point
values, the max values in row 10 would not be needed<<
Scores were averaged across a quarter. So, the following formulas
calculated the points possible, average score, and a % based off of
assignment points:
For Student X...
P13 = SUMIF(AA13:AX13,">0",AA$10:AX$10)
Q13 =SUM(AA13:AX13)
R13 =AVERAGE(IF(($AA13:AX13<>"")*($AA$10:AX$10<>0),$AA13:AX13/$AA
$10:AX$10))*100
In determining final letter grades, I did some weighting with some
other scores, but ultimately used this formula in col. X:
=IF(V13<>"",HLOOKUP(V13/100,Hgrades,2),"")
So, there were 2 cols. in a sheet named Fields that the HLOOKUP
referenced:
0 N
40 I
50 F
60 D-
63.33 D
66.67 D+
70 C-
73.33 C
76.67 C+ (In the above, a C+ would = 78.33)
80 B- (In the above, a B- would = 71.67)
83.33 B (In the above, a B would = 85.00)
86.67 B+ (In the above, a B+ would = 88.33)
90 A- (In the above, an A- would equal 91.67)
93.33 A
96.67 A+
===============================
Very curious...Thanks a lot!
Arnold