Using Excel 2003 on an XP pro machine I created a 5 sheet workbook.

On (named) sheets 2-5 column A has a date, columns B and C has a number,
column D is equal to C-B for each row. Row 1 of all four sheets is a text
label.

On sheet one I created a summary with five rows (row one is text labels
only)

Column A has a text label, B is:

=SUMIF(~sheet~!C:C,"0",~sheet~!B:B)

column C is

=SUM(~sheet~!C:C)

column D is

=SUM(~sheet~!D:D)

column E is (one example)

=1-B3/ABS(C3)

column F

=AVERAGE(IF(~sheet~!B:B<0, ~sheet~!B:B,""))

3 of the four rows in column F work. One column returns #VALUE!

When I look at the step by step evaluation it says
Reference: Summary!$F$4
Evaluation AVERAGE( "")

For the life of me I can't figure out what is going wrong here. All other
references to that sheet work, I've typed in the formula manually as well as
copied from one of the working cells then changed the name of the referenced
sheet.

I'm out of ideas - any suggestions would be greatly appreciated.