GradeBook

[WannaKooky]

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

I give 2 grades a week which I average all out at the end of a school
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 on
0-4 scale)
1 Column for Letter Grade based on addition of Lesson, Test, and Days
Missed Pts
1 Column for Letter Grade based on addition of Effort, Conduct, and
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 4
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 I
could avoid having the F there without inputting data. I don't want to
demoralize the kids.

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

Any help would be greatly appreciated. Thanks.

 

[Arvi Laanemets]

Hi

Maybe you try with an alternative design - columns like this:
Week, Pupil, DaysMissed, Lesson, Test, Effort, Conduct, Missing, Grade1,
Grade2

Enter the list of pupils into Pupil column, and copy it down 10 times. Into
Week column, enter the week numbers for every row. For last block of names,
instead week number enter 'Average'. Select cell C2, and set FreezePanes on.


You didn't explain, how are you calculated points for missed days (I removed
DaysMissed column before points columns, so all input is from adjacent
cells). For my example I used formula (for cell H2)
=IF(OR(B2="",C2=""),"",MAX(4-C2,0))
Into cell I2 enter the formula
=IF(OR(B2="",C2="",D2="",E2=""),"",CHOOSE(MATCH(SUM(D2:E2,H2),{0,5,7,9,11},1
),"F","D","C","B","A"))
Into cell J2 enter the formula
=IF(OR(B2="",C2="",F2="",G2=""),"",CHOOSE(MATCH(SUM(F2:G2,H2),{0,5,7,9,11},1
),"F","D","C","B","A"))
Copy formulas in H2:J2 down for all weeks. Copy formulas in columns I:J down
for averages too.

Into cell D##, where ## is the number of first average row, enter the
formula
=SUMIF($B$2:$B$#;$B##;D$2$#)/COUNTIF($B$2:$B$#;$B##)
where # is number of last row of last week
and copy the formula inlo all average cells in columns D:H

Set autofilter on. To enter weeks data, set autofilter to this week. To look
average data, set autofilter to 'Average'