I'm having a problem with a spreadsheet I'm developing which records average
marks of groups of students over several weeks. I want to display a
"Running Average" of those averages as I enter the data, however, because I
do this week by week there are inevitably weeks which have not had any data
entered and her I see the error #DIV/0! for the formula
=AVERAGE(IF(R5:R20<0, R5:R20,"")). I do understand why I'm getting this
(because I'm dividing by zero) but would like to return a zero so that the
average of the averages will return a number rather than an error.
Can anybody help me?


Pete

=IF(ERROR.TYPE(AVERAGE(B5:B20))=2,0,AVERAGE(B5:B20 ))

Regards,
Stefi

€žPete Cumberland€� ezt Ã*rta:

I'm having a problem with a spreadsheet I'm developing which records average
marks of groups of students over several weeks. I want to display a
"Running Average" of those averages as I enter the data, however, because I
do this week by week there are inevitably weeks which have not had any data
entered and her I see the error #DIV/0! for the formula
=AVERAGE(IF(R5:R20<0, R5:R20,"")). I do understand why I'm getting this
(because I'm dividing by zero) but would like to return a zero so that the
average of the averages will return a number rather than an error.
Can anybody help me?


Pete

What if you converted your formula to a CSE type?
Select the cell, F2 (edit) on it, then press Control key + Shift key + Enter
key simultaneously to re-enter.

"Pete Cumberland" wrote in
message ...
I'm having a problem with a spreadsheet I'm developing which records
average
marks of groups of students over several weeks. I want to display a
"Running Average" of those averages as I enter the data, however, because
I
do this week by week there are inevitably weeks which have not had any
data
entered and her I see the error #DIV/0! for the formula
=AVERAGE(IF(R5:R20<0, R5:R20,"")). I do understand why I'm getting this
(because I'm dividing by zero) but would like to return a zero so that the
average of the averages will return a number rather than an error.
Can anybody help me?


Pete

If in the workbook this will be repeated many times or involve a much
larger range, then it might recalculate faster to cut out the math
=IF(COUNT(B5:B20),AVERAGE(B5:B20),0)

Jerry

Stefi wrote:

=IF(ERROR.TYPE(AVERAGE(B5:B20))=2,0,AVERAGE(B5:B20 ))

Regards,
Stefi

€žPete Cumberland€� ezt Ã*rta:


I'm having a problem with a spreadsheet I'm developing which records average
marks of groups of students over several weeks. I want to display a
"Running Average" of those averages as I enter the data, however, because I
do this week by week there are inevitably weeks which have not had any data
entered and her I see the error #DIV/0! for the formula
=AVERAGE(IF(R5:R20<0, R5:R20,"")). I do understand why I'm getting this
(because I'm dividing by zero) but would like to return a zero so that the
average of the averages will return a number rather than an error.
Can anybody help me?


Pete

Hi Jerry,

I tried your solution and it worked, but it's new for me that COUNT(B5:B20)
used as a criterium returns a Boolean value required by the IF function as
first argument, while using as a separate function returns an integer. It
seems that if COUNT(B5:B20) returns zero then its logical value is FALSE, it
it returns a positive integer then its logical value is TRUE. Is it so? How
do you find out such tricks, Help doesn't mention this possibility?

Regards,
Srefi


€žJerry W. Lewis€� ezt Ã*rta:

If in the workbook this will be repeated many times or involve a much
larger range, then it might recalculate faster to cut out the math
=IF(COUNT(B5:B20),AVERAGE(B5:B20),0)

Jerry

Stefi wrote:

=IF(ERROR.TYPE(AVERAGE(B5:B20))=2,0,AVERAGE(B5:B20 ))

Regards,
Stefi

€žPete Cumberland€� ezt Ã*rta:


I'm having a problem with a spreadsheet I'm developing which records average
marks of groups of students over several weeks. I want to display a
"Running Average" of those averages as I enter the data, however, because I
do this week by week there are inevitably weeks which have not had any data
entered and her I see the error #DIV/0! for the formula
=AVERAGE(IF(R5:R20<0, R5:R20,"")). I do understand why I'm getting this
(because I'm dividing by zero) but would like to return a zero so that the
average of the averages will return a number rather than an error.
Can anybody help me?


Pete

Hi Jim,
It already is a CSE type I think in that I had to hit Ctrl+Shift+Enter
and it is in "curly brackets".

Pete
"Jim May" wrote in message
news:tl%df.12797$0l5.6641@dukeread06...
What if you converted your formula to a CSE type?
Select the cell, F2 (edit) on it, then press Control key + Shift key +
Enter
key simultaneously to re-enter.

"Pete Cumberland" wrote in
message ...
I'm having a problem with a spreadsheet I'm developing which records
average
marks of groups of students over several weeks. I want to display a
"Running Average" of those averages as I enter the data, however,
because
I
do this week by week there are inevitably weeks which have not had any
data
entered and her I see the error #DIV/0! for the formula
=AVERAGE(IF(R5:R20<0, R5:R20,"")). I do understand why I'm getting
this
(because I'm dividing by zero) but would like to return a zero so that
the
average of the averages will return a number rather than an error.
Can anybody help me?


Pete

Thanks Stefi but does this formula replace the one I am using or go in front
of it and if the latter how do I separate the expressions (space, Comma,
Colon)? I have tried replacement but get a comment about using = in the
formula. Also I omitted the fact that the formula was inside curly brackets
{}.

Pete

"Stefi" wrote in message
...
=IF(ERROR.TYPE(AVERAGE(B5:B20))=2,0,AVERAGE(B5:B20 ))

Regards,
Stefi

"Pete Cumberland" ezt írta:

I'm having a problem with a spreadsheet I'm developing which records
average
marks of groups of students over several weeks. I want to display a
"Running Average" of those averages as I enter the data, however,
because I
do this week by week there are inevitably weeks which have not had any
data
entered and her I see the error #DIV/0! for the formula
=AVERAGE(IF(R5:R20<0, R5:R20,"")). I do understand why I'm getting
this
(because I'm dividing by zero) but would like to return a zero so that
the
average of the averages will return a number rather than an error.
Can anybody help me?


Pete

Hi Jerry,
I tried your solution (many thanks) but as there may be blanks in the
range (represented by zero) I get an incorrect average.

Pete
"Jerry W. Lewis" wrote in message
...
If in the workbook this will be repeated many times or involve a much
larger range, then it might recalculate faster to cut out the math
=IF(COUNT(B5:B20),AVERAGE(B5:B20),0)

Jerry

Stefi wrote:

=IF(ERROR.TYPE(AVERAGE(B5:B20))=2,0,AVERAGE(B5:B20 ))

Regards,
Stefi

"Pete Cumberland" ezt írta:


I'm having a problem with a spreadsheet I'm developing which records
average
marks of groups of students over several weeks. I want to display a
"Running Average" of those averages as I enter the data, however,
because I
do this week by week there are inevitably weeks which have not had any
data
entered and her I see the error #DIV/0! for the formula
=AVERAGE(IF(R5:R20<0, R5:R20,"")). I do understand why I'm getting
this
(because I'm dividing by zero) but would like to return a zero so that
the
average of the averages will return a number rather than an error.
Can anybody help me?


Pete

Many languages will allow numbers in place of booleans, and will treat 0 as
False and any other number as True. That permits + to double for Or and * to
double for And. Many threads have used this unstated logic to handle
conditional counts, sums, averages, etc where there are multiple conditions.

You can see the primary representation for booleans in a given language
language by adding zero. Excel's primary numeric representation for TRUE is
1, as seen by
=TRUE+0
VBA's primary numeric representation for TRUE is -1, as seen by
Sub checkIt()
MsgBox True + 0
End Sub

Jerry

"Stefi" wrote:

Hi Jerry,

I tried your solution and it worked, but it's new for me that COUNT(B5:B20)
used as a criterium returns a Boolean value required by the IF function as
first argument, while using as a separate function returns an integer. It
seems that if COUNT(B5:B20) returns zero then its logical value is FALSE, it
it returns a positive integer then its logical value is TRUE. Is it so? How
do you find out such tricks, Help doesn't mention this possibility?

Regards,
Srefi

You didn't mention this additional requirement in your original post. Since
these are grades, I will assume that negative numbers cannot occur. In that
case
=IF(COUNTIF(B5:B20,"0"),SUMIF(B5:B20,"0")/COUNTIF(B5:B20,"0"),0)
may be closer to what you are looking for.

Jerry

"Pete Cumberland" wrote:

Hi Jerry,
I tried your solution (many thanks) but as there may be blanks in the
range (represented by zero) I get an incorrect average.

Pete
"Jerry W. Lewis" wrote in message
...
If in the workbook this will be repeated many times or involve a much
larger range, then it might recalculate faster to cut out the math
=IF(COUNT(B5:B20),AVERAGE(B5:B20),0)

Jerry