Average formulas

[Ed]

I am trying to average the results of several different classes testing on
differnt tests. My problem is that if in one time period one class does not
test I get an error message.
=AVERAGE(SFOA!I36,SFOB!I37,SFOC!I31,SFOD!I38,SFOE!I38,SFOF!I38) If SFOB does
not take a particular test I get the #DIV/0! error. I am trying to find the
formula to ignore blank fields. Thanks for your assistance.

 

[Jerry W. Lewis]

AVERAGE DOES ignore blank fields and non-numeric fields (other than
errors). I presume that the cells referenced in your AVERAGE() formula
themselves contain averages, and you are getting #DIV/0! from your
formula because at least one of the referenced cells contains #DIV/0!.
You can solve the problem by fixing either your current formula, as in
=AVERAGE(IF(ISNUMBER(SFOA!I36),SFOA!I36),
IF(ISNUMBER(SFOB!I36),SFOB!I36), ...)
or by fixing the referenced formulas, as in
=IF(COUNT(range)=0,"",AVERAGE(range))

Also, note that if SFOD, SFOE, and SFOF are successive spreadsheets,
then you could simplify with a 3D formula, as in
=AVERAGE(SFOA!I36,SFOB!I37,SFOC!I31,SFOD:SFOF!I38)

Jerry