How do I set an average to not count blank cells as zeros?

[athenia_1999]

I am trying to use Excel for a grade book, but can not figure out how to get
the average function to not count blank cells as zeros. The following
function is in the Averages column:

=(IF(SUM(I15:AH15),ROUND(SUM(I15:AH15)/$I$10,2),""))

the average is displayed as a percent.

 

[Tyro]

What is in cell I10?

Tyro

 

[CLR]

Maybe this...........

=SUM(I15:AH15)/COUNTIF(I15:AH15,">0")

Vaya con Dios,
Chuck, CABGx3

 

[RichardSchollar]

Hi

The AVERAGE function itself will ignore blanks or text in the average
range so you should be able to use:

=AVERAGE(I15:AH15)

Since zero values won't have affected your summation in the first
place, I am struggling to understand exactly what the problem was you
were experiencing - was it a case that I10 contained a formula along
the lines of COUNT(I15:AH15)? This won't count blanks either, but it
will count zero values (they are different).

Richard

 

[athenia_1999]

I am trying to use the gradebook by averaging method in Office 2003 or Office
XP.
I have to add two colums to the template for a final exam grade and for a
final course grade. The final course grade is 85% Average and 15% Final Exam.
Everything works as I set it up, if there is a grade in every column. The
problem comes in when I do not count a grade for a student. If a student is
absent on a day that there is a substitute, the quiz grade for that day is
left blank. There really is no way for the student to make up the grade.

I tried changing the Average column to the average function, but the formula
in the grade input column is (points earned/point value of the assignment). I
can not get the average function to work. I am going to try to set up my own
template, but will keep working with the Office template to fine tune it.

 

[Tyro]

If your grades are in A1:A5 you can use the array formula

=AVERAGE(A1:A5<>"",A1:A5)

After typing in the formula, press Ctrl+Shift+Enter. The array formula will
ignore blanks or cells that compute a blank in the average.

Tyro