GradeBook

[WannaKooky]

I've developed an amateur's gradebook. I'm not very advanced in Exce
and its formulas. Here is my format and the formulas I have used and m
questions:

I give 2 grades a week which I average all out at the end of a schoo
quarter (9 weeks).
Each week is divided into grade factors:
1 Column for Lesson (Points given based on 0-4 scale)
1 Column for Test (Points given based on 0-4 scale)
1 Column for Effort (Points given based on 0-4 scale)
1 Column for Conduct (Points given based on 0-4 scale)
1 Column for Days Missed in the Week
1 Column for Points Given Based on Missed Days (Points given based o
0-4 scale)
1 Column for Letter Grade based on addition of Lesson, Test, and Day
Missed Pts
1 Column for Letter Grade based on addition of Effort, Conduct, an
Days Missed Pts
LAST 2 COLUMNS BASED ON THIS FORMULA
=IF(C4+D4+H4>=11,"A",IF(C4+D4+H4>=9,"B",IF(C4+D4+H4>=7,"C",IF(C4+D4+H4>=5,"D",IF(C4+D4+H4<=4.99,"F")))))

This is repeated for every week (9 weeks). At the end I have
columns:
1 Column for Average of Lesson, Test, Days Missed
FORMULA
=AVERAGE(C4+D4+H4,K4+L4+P4,S4+T4+X4,AA4+AB4+AF4,AI4+AJ4+AN4,AQ4+AR4+AV4,AY4+AZ4+BD4,BG4+BH4+BL4,BO4+BP4+BT4)
1 Column for Letter Grade Based on Average
FORMULA
=IF(BW4>=11,"A",IF(BW4>=9,"B",IF(BW4>=7,"C",IF(BW4>=5,"D",IF(BW4<=4.99,"F")))))
1 Column for Average of Effort, Conduct, Days Missed
1 Column for Letter Grade Based on Average

Here is what I need:
Each Week comes out with F because of no points. I'd like to know how
could avoid having the F there without inputting data. I don't want t
demoralize the kids.

Also, I'd like the Final Grade to compute the current average and no
average out everything because it's giving the Final Grade as F base
on what I have now.

Any help would be greatly appreciated. Thanks

 

[Don Guillett]

See if this helps. It should cover all contingencies.
=IF(ISNUMBER(B21),IF(B21>=11,"A",IF(B21>=9,"B",IF(B21>=7,"C",IF(B21>=5,"D",I
F(B21>0,"F",""))))),"")