suppress #DIV/0 AND averageif [range]>0 Excel 2003

[TManche]

Excel2003: I need a weekly average for a range of cells that are formatted for %, while suppressing #DIV/0, AND averaging only cells that are non-zero % (have a number entered in the appropriate DailyPoints worksheet cell). All formulas are arrayed (Ctrl-Shft-Enter).

WklyAvg formula is {=AVERAGE(IF(B5:F5>0,B5:F5))}

1) Formula is for a weekly average of daily percentages. Daily (B5:F5): M-67% T-0% W-0% R-0% F-0%, where B5:F5 return points earned/max. points possible (source is points entered for each day on another worksheet, DailyPoints).
2) Formulas in B5:F5 --for example, B5 formula is =(DailyPoints!C5/StudentInfo!$K$5)
3) I can eliminate either the #Div/0, or non-zero cells, but not both.

To summarize the flow of data:
DailyPoints cell does not have a formula, only a number. EX: 47
Formulas in B5:F5 EX: {=(DailyPoints!C5/StudentInfo!$K$5)}
Weekly Avg. Formula: {=AVERAGE(IF(B5:F5>0,B5:F5))}

Suggestions?

[wfm007]

Use this one:

=SUMIF(B5:B11,">0",B5:B11)/COUNTIF(B5:B11,">0")

[TManche]

Use this one:

=SUMIF(B5:B11,">0",B5:B11)/COUNTIF(B5:B11,">0")

I've already tried the sumif/countif. The formula works, if there's data, but I get the #DIV/0 if the range is empty. I want both: the sumif/countif AND supression of #DIV/0. Maybe Excel 2003 can't do that. It's easy in Excel 2007!

And how do I upload a spreadsheet portion, for viewing?

[Craig K.]

In order to help you better, please upload a sample spreadsheet with any sensitive information removed.

[Ace_XL]

Enclose in IF(ISERROR(

hence,
=IF(ISERROR(AVERAGE(IF(B5:F5>0,B5:F5))),"",AVERAGE(IF(B5:F5>0,B5:F5)))
array entered

or

=IF(ISERROR(SUMIF(B5:B11,">0",B5:B11)/COUNTIF(B5:B11,">0")),"",SUMIF(B5:B11,">0",B5:B11)/COUNTIF(B5:B11,">0"))

[Craig K.]

To upload, click go advanced, and manage attachments.

I think Ace has you covered though.