GradeBook Help, conditional average

[leknows]

I was working on a Excel spreadsheet
for my wife's first grade class and I ran into a bit
of a snag. She is having a problem when she inputs
her grades because when a student is absent, she
just doesn't count the grade. So the document is
automatically calculating the grades with the absent
"a" in there as a 0 when she wants it to just not
count at all. Is there any formula or something we
can put into the spreadsheet so she doesn't have to
go through and recalculate all those grades by hand?
I will attach the spreadsheet to make the problem more concrete.

A good example of the problem is on the math sheet.

Attachment filename: nov12.zip
Download attachment: http://www.excelforum.com/attachment.php?postid=399289

 

[Bob Phillips]

Took a look at the spreadsheet, and if I get it, you want to divide by a
lesser number when there are absences. This being so, I suggest this formula
in C22 and so on

=(IF(SUM(F22:AF22),ROUND(SUM(F22:AF22)/SUMIF($F22:$AF22,"<>A",$F$16:$AF$16),
2),""))

I would also suggest that you look at conditional formattin g for the row
stripes on lines 22 down, as it will facilitate adding new rows. ALl you
need to do is
- select all the rows,
-go go Format>Conditional formatting
- change Condition1 to Formula Is
- add a formula of =MOD(ROW(),2)=1
- click Format
- select the patterns tab
- select the gray colour
- OK
-OK

automatic row colouring, which adapts if you insert a row

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[leknows]

Bob,

Thanks so much for the help. That is such a big help. You just saved
me and my wife so much time. Thanks also for the conditional
formatting, I figured there was a way to do that, but it was way out of
my league (as was the absence formula). Thanks so much!!!!

 

[Bob Phillips]

Than you, your feedback is much appreciated.

Bob