return a zero for error

[Pete Cumberland]

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

 

[Guest]

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

Regards,
Stefi

„Pete Cumberland†ezt írta:

 

[Jim May]

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.

 

[Jerry W. Lewis]

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

 

[Guest]

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:

 

[Pete Cumberland]

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

 

[Pete Cumberland]

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

 

[Pete Cumberland]

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

 

[Guest]

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

 

[Guest]

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