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

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.

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

What is in cell I10?

Tyro

"athenia_1999" wrote in message
...
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.

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

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

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

Vaya con Dios,
Chuck, CABGx3


"athenia_1999" wrote in message
...
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.

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

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

On 7 Jan, 01:30, athenia_1999
wrote:
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.

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

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.

"RichardSchollar" wrote:

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

On 7 Jan, 01:30, athenia_1999
wrote:
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.

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

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
"athenia_1999" wrote in message
...
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.

"RichardSchollar" wrote:

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

On 7 Jan, 01:30, athenia_1999
wrote:
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.