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

Why not just put the computed averages to the right of the 2nd column in your
table. then you could lookup the Letter grade with Hlookup and return the
average.

=if(V3<>"",if(isnumber(V13),HLOOKUP(V13/100,Hgrades,2),""),HLOOKUP(130,Offset(Hgrades,0,1),2),""),"")

--
Regards,
Tom Ogilvy

"Arnold" wrote:

> 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
>
>

Thanks for responding Tom,
My mistake on the statement "2 cols. in a sheet named Fields that the
HLOOKUP
referenced"--The HLOOKUP is horizontal, occupies 15 columns, is
=Fields!$J$1:$X$4, and now looks something like:

% Score 0.00% 40.00% 50.00% 60.00% 63.33% 66.67% 70.00%
Ltr Grade NC INC F D- D D+ C-
GPA 0.00 0.00 0.33 0.67 1.00 1.33 1.67
Average 0.00 45.00 55.00 61.67 65.00 68.33 71.67 and so on...

with the mid points of the grade ranges now put on row 4.

However, col. V on the grades sheet is dependent on the values in
cols. P, Q, and R, and it would need to be these cols. that could deal
with the mix of letters and number scores in calculations.

I guess that would make sense since you were using HLOOKUP

=IF(I7="","",IF(ISNUMBER(I7),HLOOKUP(I7/100,Fields!$J$1:$X$4,2,TRUE),HLOOKUP(I7,Fields!$J$2:$X$4,3,FALSE)))

when I entered 45 in I7, i returned INC

when I entered D- in I7 it retuned 61.67

which is what I expected.



--
Regards,
Tom Ogilvy




"Arnold" wrote:

> Thanks for responding Tom,
> My mistake on the statement "2 cols. in a sheet named Fields that the
> HLOOKUP
> referenced"--The HLOOKUP is horizontal, occupies 15 columns, is
> =Fields!$J$1:$X$4, and now looks something like:
>
> % Score 0.00% 40.00% 50.00% 60.00% 63.33% 66.67% 70.00%
> Ltr Grade NC INC F D- D D+ C-
> GPA 0.00 0.00 0.33 0.67 1.00 1.33 1.67
> Average 0.00 45.00 55.00 61.67 65.00 68.33 71.67 and so on...
>
> with the mid points of the grade ranges now put on row 4.
>
> However, col. V on the grades sheet is dependent on the values in
> cols. P, Q, and R, and it would need to be these cols. that could deal
> with the mix of letters and number scores in calculations.
>
>